本文共 3012 字，大约阅读时间需要 10 分钟。

SELECT     d.name AS 英文表名, ISNULL(h.value, N'') AS 中文表名,  a.colorder AS 字段序号, a.name AS 英文列名, ISNULL(g.value, N'')                        AS 中文列名, CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识列,                        CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsComputed') = 1 THEN '√' ELSE '' END AS 计算列,                        CASE WHEN EXISTS                           (SELECT     1                             FROM          dbo.sysindexes si(readpast) INNER JOIN                                                    dbo.sysindexkeys sik(readpast) ON si.id = sik.id AND si.indid = sik.indid INNER JOIN                                                    dbo.syscolumns sc(readpast) ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN                                                    dbo.sysobjects so(readpast) ON so.name = si.name AND so.xtype = 'PK'                             WHERE      sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键, ISNULL                           ((SELECT     TOP (1) definition                               FROM         sys.check_constraints WITH (readpast)                               WHERE     (parent_object_id = d.id) AND (parent_column_id = a.colid)), N'') AS Check约束, ISNULL(e.text, N'') AS 默认值, ISNULL                           ((SELECT     TOP (1) cx.name                               FROM         sys.syscolumns AS ax WITH (readpast) INNER JOIN                                                     sys.sysobjects AS bx WITH (readpast) ON ax.id = bx.id INNER JOIN                                                     sys.sysobjects AS cx WITH (readpast) ON ax.domain = cx.id                               WHERE     (d.id = bx.id) AND (a.colid = ax.colid)), N'') AS 绑定规则, ISNULL                           ((SELECT     TOP (1) ee.name                               FROM         sys.foreign_key_columns AS aa WITH (readpast) INNER JOIN                                                     sys.syscolumns AS bb WITH (readpast) ON aa.parent_column_id = bb.colid INNER JOIN                                                     sys.sysobjects AS cc WITH (readpast) ON aa.parent_object_id = cc.id AND bb.id = cc.id INNER JOIN                                                     sys.syscolumns AS dd WITH (readpast) ON aa.referenced_column_id = dd.colid INNER JOIN                                                     sys.sysobjects AS ee WITH (readpast) ON aa.referenced_object_id = ee.id AND dd.id = ee.id                               WHERE     (bb.colid = a.colid) AND (cc.id = d.id)), N'') AS 参照表, ISNULL                           ((SELECT     TOP (1) dd.name                               FROM         sys.foreign_key_columns AS aa WITH (readpast) INNER JOIN                                                     sys.syscolumns AS bb WITH (readpast) ON aa.parent_column_id = bb.colid INNER JOIN                                                     sys.sysobjects AS cc WITH (readpast) ON aa.parent_object_id = cc.id AND bb.id = cc.id INNER JOIN                                                     sys.syscolumns AS dd WITH (readpast) ON aa.referenced_column_id = dd.colid INNER JOIN                                                     sys.sysobjects AS ee WITH (readpast) ON aa.referenced_object_id = ee.id AND dd.id = ee.id                               WHERE     (bb.colid = a.colid) AND (cc.id = d.id)), N'') AS 参照列, b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 精度,                        ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数, d.crdate AS 创建时间, CASE WHEN a.colorder = 1 THEN d .refdate ELSE NULL                        END AS 更改时间 FROM         dbo.syscolumns AS a WITH (readpast) LEFT OUTER JOIN                       dbo.systypes AS b WITH (readpast) ON a.xtype = b.xusertype INNER JOIN                       dbo.sysobjects AS d WITH (readpast) ON a.id = d.id AND d.xtype = 'U' AND d.name NOT IN ('sysdiagrams') AND d.status >= 0 LEFT OUTER JOIN                       dbo.syscomments AS e WITH (readpast) ON a.cdefault = e.id LEFT OUTER JOIN                       sys.extended_properties AS g WITH (readpast) ON a.id = g.major_id AND a.colid = g.minor_id AND g.name = 'MS_Description' LEFT OUTER JOIN                       sys.extended_properties AS h WITH (readpast) ON a.id = h.major_id AND 0 = h.minor_id AND h.name = 'MS_Description'

转载地址：http://jmfmi.baihongyu.com/