mssql 返回表的创建语句，mssql返回创建语句

mssql 返回表的创建语句，mssql返回创建语句

if OBJECT_ID('sp_create_table_sql','P') is not null
drop proc sp_create_table_sql
go
create proc sp_create_table_sql ( @tablename varchar(255) ) 
as 
begin
	 -- exec sp_create_table_sql 'Ad_AdGroup'
	 -- 0. 弘恩
	 -- 1. 不支持非主键类的索引
	 -- 2. 不支持主分键的非默认排序
	 -- 3. 不支持DEFAULT
	 -- 4. 不支持计算列
	 -- 5. 待完整 
	declare @sql_create varchar(max) = '';
	declare @sql_column varchar(max);
	declare @sql_primary varchar(max);
	
	with cte as 
	(
		select  QUOTENAME( c.name )+' '+
				TYPE_NAME(c.system_type_id)+' '+
				case when  TYPE_NAME( c.system_type_id) in  ('char','varchar','decimal')  then ' ( '  else ''  end +
				case when  TYPE_NAME( c.system_type_id) in  ('char','varchar','nvarchar'  )  then cast(max_length as varchar)  else ''  end+
				case when  TYPE_NAME( c.system_type_id) in  ('decimal'  )  then cast(c.precision as varchar)+','+cast(c.scale AS varchar) else ''  end+  
				case when  TYPE_NAME( c.system_type_id) in  ('char','varchar','decimal')  then ' ) ' else ''  end +
				case when c.is_nullable = 1 then ' null ' else ' not null ' end +
				case when c.is_identity = 0 then ' ' else ' identity ' end  sqlstr ,  
				
				column_id
		 from sys.objects as o
		 join sys.columns as c on o.object_id = c.object_id 
		 where o.name = @tablename and o.type = 'U'
	 )
	select @sql_column = stuff(
	(select  ',' + sqlstr  + CHAR(10)
	from cte 
	order by column_id asc 
	for xml path('') ),1,1,'')
	 ;
	
	select @sql_primary = stuff(( 
	 select ',' + c.name
	 from sys.index_columns as i 
	 join sys.indexes as ix on i.object_id = ix.object_id  and i.index_id = ix.index_id
	 join sys.columns as c on i.object_id = c.object_id  and i.column_id = c.column_id
	 where OBJECT_NAME(i.object_id) = @tablename
	 and ix.is_primary_key = 1
	  order by i.key_ordinal
	 for xml path('')
	 ),1,1,'')

	 
	 set @sql_create = ' create table ' + @tablename + '( ' 
	 +   @sql_column  
	 + case when len(@sql_primary) >= 1 then  (', primary key ( ' + @sql_primary + ')') else '' end 
	 + ' ) '
	 
	 
	print ' -- @sql_create -- '
	print @sql_create
end  

首先，这个存储过程的功能是：
输入一个表名称，执行这个存储过程，返回这个表里的每条记录的一个insert 语句。
（例如表A(a,b,c)有三条记录(1,2,3;4,5,6;7,8,9）,那么执行这个存储过程后，返回结果为：
INSERT INTO A VALUES('1','2','3')
INSERT INTO A VALUES('4','5','6')
INSERT INTO A VALUES('7','8','9')
-----------------------------------------------------------
大概解释一下这个过程：
ALTER proc --修改存储过程
DECLARE xCursor CURSOR FOR --定义游标
OPEN xCursor
FETCH xCursor into @F1,@F2 --打开游标，并给变量赋值，循环开始

WHILE @@FETCH_STATUS = 0 --通过全局变量 @@FETCH_STATUS 的值来判断当前游标是否有效（=0代表游标取值成功）
CLOSE xCursor --关闭游标
exec (@sql) --执行动态sql语句，@sql就是通过游标拼装的sql语句。

希望可以帮助到你

 

右键点表名