1 SELECT CASE WHEN col.colorder = 1 THEN obj.name ELSE '' END AS 表名, col.colorder AS 序号, col.name AS 列名,
2 ISNULL(ep.value, N'') AS 列说明, t.name AS 数据类型, col.length AS 长度, ISNULL(COLUMNPROPERTY(col.id, col.name,
3 'Scale'), 0) AS 小数位数, CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity')
4 = 1 THEN '√' ELSE '' END AS 标识, CASE WHEN EXISTS
5 (SELECT 1
6 FROM dbo.sysindexes si INNER JOIN
7 dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
8 dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
9 dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
10 WHERE sc.id = col.id AND sc.colid = col.colid) THEN '√' ELSE '' END AS 主键,
11 CASE WHEN col.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(comm.text, N'') AS 默认值
12 FROM syscolumns AS col LEFT OUTER JOIN
13 systypes AS t ON col.xtype = t.xusertype INNER JOIN
14 sysobjects AS obj ON col.id = obj.id AND obj.xtype = 'U' AND obj.status >= 0 LEFT OUTER JOIN
15 syscomments AS comm ON col.cdefault = comm.id LEFT OUTER JOIN
16 sys.extended_properties AS ep ON col.id = ep.major_id AND col.colid = ep.minor_id AND
17 ep.name = 'MS_Description' LEFT OUTER JOIN
18 sys.extended_properties AS epTwo ON obj.id = epTwo.major_id AND epTwo.minor_id = 0 AND
19 epTwo.name = 'MS_Description'
20 WHERE (obj.name = 'TB_ROOM_STATE')
21 ORDER BY 序号
