查询表结构生成建表语句

t1292582463

--给@tablename赋值，可赋值完整表名或部分表名，查询出语句为一行一表，包含注释
DECLARE @tablename nvarchar(200) = '表名或表前缀'
SELECT ISNULL('--' +  CAST(t2.value AS NVARCHAR(256)) ,'') + CHAR(13) + 'CREATE TABLE ' + t.name + CHAR(13)+ '(' + CHAR(13) + REPLACE('        '+t1.column_name,'∞',CHAR(13)) + ')' + CHAR(13) + 'GO' AS column_name,t.name
FROM sys.sysobjects t
OUTER APPLY(
SELECT STUFF((SELECT  name  + '∞' FROM (SELECT ('        ' + CAST(a.name AS NVARCHAR(256)) +' '+ UPPER(b.name)+
(CASE WHEN CHARINDEX(b.name,'NVARCHAR,CHAR,VARCHAR') > 0 THEN '('+ (CASE WHEN a.length = -1 THEN 'MAX' ELSE CAST(a.length AS NVARCHAR(256)) END) +')' WHEN CHARINDEX(b.name,'DECIMAL') > 0 THEN '('+  CAST(a.xprec AS NVARCHAR(256))+','+CAST(a.xscale AS NVARCHAR(256))+')' ELSE '' END)  
+ ' ' + (CASE a.isnullable WHEN 0 THEN 'NOT NUll' ELSE 'NUll' END) + ' '+ (CASE pk.ISPK WHEN 1 THEN 'PRIMARY KEY' ELSE '' END)+(CASE WHEN (SELECT MAX(colorder) FROM sys.syscolumns WHERE id = t.id) = a.colorder THEN '' ELSE  ',' END)
+ ISNULL('  --' +CAST(c.value AS NVARCHAR(256)),'')) AS name
FROM sys.syscolumns a
LEFT JOIN sys.systypes b ON b.xusertype=a.xtype
LEFT JOIN sys.extended_properties c ON c.major_id = t.id AND c.minor_id = a.colid AND c.name = 'MS_Description'
OUTER APPLY((SELECT COUNT(1) AS ISPK FROM sys.sysobjects WHERE parent_obj = t.id AND name = (SELECT TOP 1 name FROM sys.sysindexes ind INNER JOIN sys.sysindexkeys indkey ON ind.indid = indkey.indid AND indkey.colid = a.colid AND indkey.id = t.id WHERE ind.id = t.id AND ind.name LIKE 'PK_%'))) pk
WHERE a.id = t.id)ty FOR XML PATH('')),1,1,'') AS column_name
)t1
LEFT JOIN sys.extended_properties t2 ON t2.major_id = t.id  AND t2.minor_id = 0
WHERE t.type = 'u' AND CHARINDEX(@table,t.name) > 0;