你的位置:首页 > 数据库

[数据库]查询表结构sql


 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 JOIN13         systypes AS t ON col.xtype = t.xusertype INNER JOIN14         sysobjects AS obj ON col.id = obj.id AND obj.xtype = 'U' AND obj.status >= 0 LEFT OUTER JOIN15         syscomments AS comm ON col.cdefault = comm.id LEFT OUTER JOIN16         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 JOIN18         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 序号