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

这篇CFSDN的博客文章探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

接着上篇文章《解析SQL 表结构信息查询 含主外键、自增长》里面提到了INFORMATION_SCHEMA视图，其实到了SQL 2005微软都主推大家使用INFORMATION_SCHEMA系统视图，而不是在使用sys东东了，当然目前还是有许多信息只能通过sys视图来查询。这里我们还是以查询表结果信息为例来说明一些主要的INFORMATION_SCHEMA视图的使用。 首先我们需要查询列的信息，这需要用到[INFORMATION_SCHEMA].[COLUMNS]系统视图来查询数据列的信息，SQL 如下:

复制代码代码如下

SELECT  c.TABLE_SCHEMA ,         c.TABLE_NAME ,         c.COLUMN_NAME ,         c.DATA_TYPE ,         c.CHARACTER_MAXIMUM_LENGTH ,         c.COLUMN_DEFAULT ,         c.IS_NULLABLE ,         c.NUMERIC_PRECISION ,         c.NUMERIC_SCALE FROM    [INFORMATION_SCHEMA].[COLUMNS] c WHERE   TABLE_NAME = 'Address' 。

运行结果如下： 我们都知道我们在定义列的时候一般的使用都是varchar(50)之类的信息，这里我们需要整合DATA_TYPE和CHARACTER_MAXIMUM_LENGTH信息，当CHARACTER_MAXIMUM_LENGTH为-1时即使说没有指定具体最大长度，数据的指定长度信息是max，而numeric需要整合NUMERIC_PRECISION、NUMERIC_SCALE信息。修改后的SQL如下:

复制代码代码如下

SELECT  c.TABLE_SCHEMA ,         c.TABLE_NAME ,         c.COLUMN_NAME ,         CASE WHEN ( ( CHARINDEX('char', c.DATA_TYPE) > 0                       OR CHARINDEX('binary', c.DATA_TYPE) > 0                     )                     AND c.CHARACTER_MAXIMUM_LENGTH <> -1                   )              THEN c.DATA_TYPE + '('                   + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')'              WHEN ( ( CHARINDEX('CHAR', c.DATA_TYPE) > 0                       OR CHARINDEX('binary', c.DATA_TYPE) > 0                     )                     AND c.CHARACTER_MAXIMUM_LENGTH = -1                   ) THEN c.DATA_TYPE + '(max)'              WHEN ( CHARINDEX('numeric', c.DATA_TYPE) > 0 )              THEN c.DATA_TYPE + '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4))                   + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + ')'              ELSE c.DATA_TYPE         END AS DATA_TYPE ,         c.COLUMN_DEFAULT ,         c.IS_NULLABLE ,         c.COLUMN_DEFAULT FROM    [INFORMATION_SCHEMA].[COLUMNS] c WHERE   TABLE_NAME = 'Address' ORDER BY c.ORDINAL_POSITION 。

运行结果如图：   现在我们需要标记这张表的那些列是主键，那些列是外键，要查询表的主、外键信息需要用到[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]和[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] 系统视图 运行结果如图： 修改我们先前的SQL语句:

复制代码代码如下

SELECT  c.TABLE_SCHEMA ,         c.TABLE_NAME ,         c.COLUMN_NAME ,         CASE WHEN ( ( CHARINDEX('char', c.DATA_TYPE) > 0                       OR CHARINDEX('binary', c.DATA_TYPE) > 0                     )                     AND c.CHARACTER_MAXIMUM_LENGTH <> -1                   )              THEN c.DATA_TYPE + '('                   + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')'              WHEN ( ( CHARINDEX('CHAR', c.DATA_TYPE) > 0                       OR CHARINDEX('binary', c.DATA_TYPE) > 0                     )                     AND c.CHARACTER_MAXIMUM_LENGTH = -1                   ) THEN c.DATA_TYPE + '(max)'              WHEN ( CHARINDEX('numeric', c.DATA_TYPE) > 0 )              THEN c.DATA_TYPE + '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4))                   + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + ')'              ELSE c.DATA_TYPE         END AS DATA_TYPE ,         c.COLUMN_DEFAULT ,         c.IS_NULLABLE ,         c.COLUMN_DEFAULT ,         CASE WHEN tc.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 'YES'              ELSE 'NO'         END AS IS_PRIMARY_KEY ,         CASE WHEN tc.CONSTRAINT_TYPE = 'FOREIGN KEY' THEN 'YES'              ELSE 'NO'         END AS IS_FOREIGN_KEY FROM    [INFORMATION_SCHEMA].[COLUMNS] c         LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] kcu ON kcu.TABLE_SCHEMA = c.TABLE_SCHEMA                                                               AND kcu.TABLE_NAME = c.TABLE_NAME                                                               AND kcu.COLUMN_NAME = c.COLUMN_NAME         LEFT JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] tc ON tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA                                                               AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME WHERE   c.TABLE_NAME = 'Address' ORDER BY c.ORDINAL_POSITION 。

运行结果如图： 现在我们已经知道那些列是主键那些是外键，接下来的就是外键列所关联的外检表信息，这里需要用到[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS]系统视图，运行该视图如下： 所以修改我们的SQL如下:

复制代码代码如下

SELECT  c.TABLE_SCHEMA ,         c.TABLE_NAME ,         c.COLUMN_NAME ,         CASE WHEN ( ( CHARINDEX('char', c.DATA_TYPE) > 0                       OR CHARINDEX('binary', c.DATA_TYPE) > 0                     )                     AND c.CHARACTER_MAXIMUM_LENGTH <> -1                   )              THEN c.DATA_TYPE + '('                   + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')'              WHEN ( ( CHARINDEX('CHAR', c.DATA_TYPE) > 0                       OR CHARINDEX('binary', c.DATA_TYPE) > 0                     )                     AND c.CHARACTER_MAXIMUM_LENGTH = -1                   ) THEN c.DATA_TYPE + '(max)'              WHEN ( CHARINDEX('numeric', c.DATA_TYPE) > 0 )              THEN c.DATA_TYPE + '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4))                   + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + ')'              ELSE c.DATA_TYPE         END AS DATA_TYPE ,         c.COLUMN_DEFAULT ,         c.IS_NULLABLE ,         c.COLUMN_DEFAULT ,         CASE WHEN tc.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 'YES'              ELSE 'NO'         END AS IS_PRIMARY_KEY ,         CASE WHEN tc.CONSTRAINT_TYPE = 'FOREIGN KEY' THEN 'YES'              ELSE 'NO'         END AS IS_FOREIGN_KEY,         fkcu.COLUMN_NAME AS FOREIGN_KEY,         fkcu.TABLE_NAME AS FOREIGN_TABLE FROM    [INFORMATION_SCHEMA].[COLUMNS] c         LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] kcu ON kcu.TABLE_SCHEMA = c.TABLE_SCHEMA                                                               AND kcu.TABLE_NAME = c.TABLE_NAME                                                               AND kcu.COLUMN_NAME = c.COLUMN_NAME         LEFT JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] tc ON tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA                                                               AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME         LEFT JOIN [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] fc ON kcu.CONSTRAINT_SCHEMA = fc.CONSTRAINT_SCHEMA                                                               AND kcu.CONSTRAINT_NAME = fc.CONSTRAINT_NAME         LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] fkcu ON fkcu.CONSTRAINT_SCHEMA = fc.UNIQUE_CONSTRAINT_SCHEMA                                                               AND fkcu.CONSTRAINT_NAME = fc.UNIQUE_CONSTRAINT_NAME WHERE   c.TABLE_NAME = 'Address' ORDER BY c.ORDINAL_POSITION 。

运行结果如图： 我们的查询结果中显示了太多的NULL，看着不怎么舒服，还有我们的表名应该显示一次就可以，修改SQL如下:

复制代码代码如下

SELECT  CASE WHEN c.ORDINAL_POSITION = 1              THEN c.TABLE_SCHEMA + '.' + c.TABLE_NAME              ELSE ''         END AS TABLE_NAME ,         c.COLUMN_NAME ,         CASE WHEN ( ( CHARINDEX('char', c.DATA_TYPE) > 0                       OR CHARINDEX('binary', c.DATA_TYPE) > 0                     )                     AND c.CHARACTER_MAXIMUM_LENGTH <> -1                   )              THEN c.DATA_TYPE + '('                   + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')'              WHEN ( ( CHARINDEX('CHAR', c.DATA_TYPE) > 0                       OR CHARINDEX('binary', c.DATA_TYPE) > 0                     )                     AND c.CHARACTER_MAXIMUM_LENGTH = -1                   ) THEN c.DATA_TYPE + '(max)'              WHEN ( CHARINDEX('numeric', c.DATA_TYPE) > 0 )              THEN c.DATA_TYPE + '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4))                   + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + ')'              ELSE c.DATA_TYPE         END AS DATA_TYPE ,         ISNULL(c.COLUMN_DEFAULT, '') AS COLUMN_DEFAULT ,         CASE WHEN c.IS_NULLABLE = 'YES' THEN '√'              ELSE ''         END IS_NULLABLE ,         CASE WHEN tc.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN '√'              ELSE ''         END AS IS_PRIMARY_KEY ,         CASE WHEN tc.CONSTRAINT_TYPE = 'FOREIGN KEY' THEN '√'              ELSE ''         END AS IS_FOREIGN_KEY ,         ISNULL(fkcu.COLUMN_NAME, '') AS FOREIGN_KEY ,         ISNULL(fkcu.TABLE_NAME, '') AS FOREIGN_TABLE FROM    [INFORMATION_SCHEMA].[COLUMNS] c         LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] kcu ON kcu.TABLE_SCHEMA = c.TABLE_SCHEMA                                                               AND kcu.TABLE_NAME = c.TABLE_NAME                                                               AND kcu.COLUMN_NAME = c.COLUMN_NAME         LEFT JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] tc ON tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA                                                               AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME         LEFT JOIN [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] fc ON kcu.CONSTRAINT_SCHEMA = fc.CONSTRAINT_SCHEMA                                                               AND kcu.CONSTRAINT_NAME = fc.CONSTRAINT_NAME         LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] fkcu ON fkcu.CONSTRAINT_SCHEMA = fc.UNIQUE_CONSTRAINT_SCHEMA                                                               AND fkcu.CONSTRAINT_NAME = fc.UNIQUE_CONSTRAINT_NAME WHERE   c.TABLE_NAME = 'Address' ORDER BY c.ORDINAL_POSITION 。

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

最后此篇关于探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息的文章就讲到这里了,如果你想了解更多关于探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。