分享：sp_create_table_sql sqlserver获取创建表结构

use master    
go    
    
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( ltrim(rtrim(c.name)) )+' '+      
                TYPE_NAME(c.system_type_id)+' '+      
                case when  TYPE_NAME( c.system_type_id) in  ('char','varchar','decimal','nchar','nvarchar')  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','nchar','nvarchar')  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      
    select @sql_create  
end        
    
GO    
EXEC sp_MS_marksystemobject 'sp_create_table_sql'      
GO