CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章解析SQL 表结构信息查询 含主外键、自增长由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

最近项目需要做什么数据字典，需要表结构信息。在网上看了许多关于表结构信息的查询，感觉都不怎么样。相对好一点就是《基于SQL2005 SQL2008 表结构信息查询升级版的详解(含外键信息)》 ，但是这里有一点小问题，缺少一个过滤以致运行有一点小bug。在AdventureWorks2012数据库中的Address表查询结果如图:

在查询过滤中我们添加以下信息就ok了： AND g.class_desc = 'OBJECT_OR_COLUMN' 修改后的SQL如下:

复制代码代码如下

SELECT  表名 = CASE WHEN a.colorder = 1 THEN d.name                   ELSE ''              END ,         表说明 = CASE WHEN a.colorder = 1 THEN ISNULL(f.value, '')                    ELSE ''               END ,         字段序号 = a.colorder ,         字段名 = a.name ,         标识 = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√'                   ELSE ''              END ,         主键 = CASE WHEN EXISTS ( SELECT  1                                 FROM    dbo.sysindexes si                                         INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id                                                               AND si.indid = sik.indid                                         INNER JOIN dbo.syscolumns sc ON sc.id = sik.id                                                               AND sc.colid = sik.colid                                         INNER JOIN dbo.sysobjects so ON so.name = so.name                                                               AND so.xtype = 'PK'                                 WHERE   sc.id = a.id                                         AND sc.colid = a.colid ) THEN '√'                   ELSE ''              END ,         外键 = CASE WHEN tony.fkey IS NOT NULL                        AND tony.fkey = a.colid THEN '√'                   ELSE ''              END ,         外键表 = CASE WHEN tony.fkey IS NOT NULL                         AND tony.fkey = a.colid THEN OBJECT_NAME(tony.fkeyid)                    ELSE ''               END ,         外键字段 = CASE WHEN tony.fkey IS NOT NULL                          AND tony.fkey = a.colid                     THEN ( SELECT   name                            FROM     syscolumns                            WHERE    colid = tony.fkey                                     AND id = tony.fkeyid                          )                     ELSE ''                END ,         类型 = b.name ,         长度 = a.length ,         精度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION') ,         小数位数 = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) ,         允许空 = CASE WHEN a.isnullable = 1 THEN '√'                    ELSE ''               END ,         默认值 = ISNULL(e.text, '') ,         字段说明 = ISNULL(g.[value], '') ,         创建时间 = d.crdate ,         更改时间 = CASE WHEN a.colorder = 1 THEN d.refdate                     ELSE NULL                END FROM    dbo.syscolumns a         LEFT JOIN dbo.systypes b ON a.xtype = b.xusertype         INNER JOIN dbo.sysobjects d ON a.id = d.id                                        AND d.xtype = 'U'                                        AND d.status >= 0         LEFT JOIN dbo.syscomments e ON a.cdefault = e.id         LEFT JOIN sys.extended_properties g ON a.id = g.major_id                                                AND a.colid = g.minor_id                                                AND g.class_desc = 'OBJECT_OR_COLUMN'         LEFT JOIN sys.extended_properties f ON d.id = f.major_id                                                AND f.minor_id = 0         LEFT JOIN sysobjects htl ON htl.parent_obj = d.id                                     AND htl.xtype = 'F'         LEFT JOIN sysforeignkeys tony ON htl.id = tony.constid WHERE   d.name = 'Address'  --这里输入包含表名称的条件 ORDER BY d.id ,         a.colorder 。

运行结果如图：  。

我不怎么喜欢它的“类型”信息，一般的varchar都会有长度信息，还有这个查询对于SQL 2012的新数据类型不支持，该SQL里面的嵌套查询比较多，于是我就自己重新写了一个SQL。 这里提醒大家尽量用INFORMATION_SCHEMA.XXX视图而不去用sys.XXX视图。 新的SQL如下:

复制代码代码如下

SELECT   --OBJECT_ID(a.TABLE_SCHEMA + '.' + a.TABLE_NAME) AS [object_id] ,         CASE WHEN a.ORDINAL_POSITION = 1              THEN a.TABLE_SCHEMA + '.' + a.TABLE_NAME              ELSE ''         END AS TABLE_NAME ,         CASE WHEN ( a.ORDINAL_POSITION = 1                     AND p1.value IS NOT NULL                   ) THEN p1.value              ELSE ''         END AS TABLE_Description ,         a.COLUMN_NAME ,         CASE WHEN ( ( CHARINDEX('char', a.DATA_TYPE) > 0                       OR CHARINDEX('binary', a.DATA_TYPE) > 0                     )                     AND a.CHARACTER_MAXIMUM_LENGTH <> -1                   )              THEN a.DATA_TYPE + '('                   + CAST(a.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')'              WHEN ( ( CHARINDEX('CHAR', a.DATA_TYPE) > 0                       OR CHARINDEX('binary', a.DATA_TYPE) > 0                     )                     AND a.CHARACTER_MAXIMUM_LENGTH = -1                   ) THEN a.DATA_TYPE + '(max)'              WHEN ( CHARINDEX('numeric', a.DATA_TYPE) > 0 )              THEN a.DATA_TYPE + '(' + CAST(a.NUMERIC_PRECISION AS VARCHAR(4))                   + ',' + CAST(a.NUMERIC_SCALE AS VARCHAR(4)) + ')'              ELSE a.DATA_TYPE         END AS COLUMN_TYPE ,         CASE WHEN c.IS_IDENTITY = 1 THEN 'YES'              ELSE 'NO'         END AS IS_IDENTITY ,         a.IS_NULLABLE ,         CASE WHEN a.COLUMN_DEFAULT IS NULL THEN ''              ELSE a.COLUMN_DEFAULT         END AS Default_Value ,         CASE WHEN p.value IS NULL THEN ''              ELSE p.value         END AS [COLUMN_Description] ,         CASE WHEN o.name IS  NULL THEN ''              ELSE '√'         END AS Is_PrimaryKey ,         CASE WHEN f.parent_column_id IS NULL THEN ''              ELSE '√'         END AS Is_Foreignkeys ,         CASE WHEN referenced_object_id IS NULL THEN ''              ELSE OBJECT_NAME(referenced_object_id)         END AS Foreign_Table ,         CASE WHEN referenced_object_id IS NULL THEN ''              ELSE ( SELECT  name                     FROM    sys.columns                     WHERE   object_id = f.referenced_object_id                             AND column_id = f.referenced_column_id                   )         END AS Foreign_key FROM    INFORMATION_SCHEMA.COLUMNS a         INNER JOIN sys.columns c ON OBJECT_ID(a.TABLE_SCHEMA + '.'                                               + a.TABLE_NAME) = c.OBJECT_ID                                     AND a.COLUMN_NAME = c.NAME         LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA                                                            AND a.TABLE_NAME = b.TABLE_NAME                                                            AND a.COLUMN_NAME = b.COLUMN_NAME         LEFT JOIN sys.sysobjects o ON o.name = b.CONSTRAINT_NAME                                       AND o.xtype = 'PK'         LEFT JOIN sys.extended_properties p ON OBJECT_ID(a.TABLE_SCHEMA + '.'                                                          + a.TABLE_NAME) = p.major_id                                                AND a.Ordinal_position = p.minor_id                                                AND p.class_desc = 'OBJECT_OR_COLUMN'         LEFT JOIN sys.extended_properties p1 ON OBJECT_ID(a.TABLE_SCHEMA + '.'                                                           + a.TABLE_NAME) = p1.major_id                                                 AND p1.minor_id = 0         LEFT JOIN SYS.foreign_key_columns f ON OBJECT_ID(a.TABLE_SCHEMA + '.'                                                          + a.TABLE_NAME) = f.parent_object_id                                                AND a.ORDINAL_POSITION = f.parent_column_id WHERE   a.TABLE_NAME = 'Address' -- a.TABLE_NAME IN (SELECT name FROM sys.tables) ORDER BY a.TABLE_SCHEMA,a.TABLE_NAME, a.ORDINAL_POSITION 。

运行效果如图： 有不对的地方还请大家拍砖！谢谢！ 。

最后此篇关于解析SQL 表结构信息查询 含主外键、自增长的文章就讲到这里了,如果你想了解更多关于解析SQL 表结构信息查询 含主外键、自增长的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。