mssql返回表的创建语句

if OBJECT_ID(sp_create_table_sql,P) is not nulldrop proc sp_create_table_sqlgocreate proc sp_create_table_sql ( @tablename varchar(255) ) as begin -- exec sp_create_table_sql Ad_AdGroup -- 0. 弘恩 -- 1. 不支持非主键类的索引 -- 2. 不支持主

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