Mysql必读探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息

概述介绍《Mysql必读探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息》开发教程，希望对您有用。

《MysqL必读探讨sql利用informatION_SCHEMA系统视图如何获取表的主外键信息》要点：
本文介绍了MysqL必读探讨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,
        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
                    )
                    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
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,
        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,
        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,
        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


运行结果如图：

有不对的地方还请大家拍砖! 总结

以上是内存溢出为你收集整理的Mysql必读探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息全部内容，希望文章能够帮你解决Mysql必读探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。