探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含9163字,纯文字阅读大概需要14分钟。
内容图文
![探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息](/upload/InfoBanner/zyjiaocheng/586/8aa58a56814040a0bb005a836beab2f9.jpg)
首先我们需要查询列的信息,这需要用到[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'
运行结果如下:
![探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息 - 文章图片](/upload/getfiles/0001/2021/4/26/20210426125127275.jpg)
我们都知道我们在定义列的时候一般的使用都是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
运行结果如图:
![探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息 - 文章图片](/upload/getfiles/0001/2021/4/26/20210426125127360.jpg)
现在我们需要标记这张表的那些列是主键,那些列是外键,要查询表的主、外键信息需要用到[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]和[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] 系统视图
运行结果如图:
![探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息 - 文章图片](/upload/getfiles/0001/2021/4/26/20210426125127548.jpg)
修改我们先前的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
运行结果如图:
![探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息 - 文章图片](/upload/getfiles/0001/2021/4/26/20210426125127647.jpg)
现在我们已经知道那些列是主键那些是外键,接下来的就是外键列所关联的外检表信息,这里需要用到[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS]系统视图,运行该视图如下:
![探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息 - 文章图片](/upload/getfiles/0001/2021/4/26/20210426125127745.jpg)
所以修改我们的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
运行结果如图:
![探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息 - 文章图片](/upload/getfiles/0001/2021/4/26/20210426125127817.jpg)
我们的查询结果中显示了太多的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系统视图如何获取表的主外键信息 - 文章图片](/upload/getfiles/0001/2021/4/26/20210426125127892.jpg)
有不对的地方还请大家拍砖!
内容总结
以上是互联网集市为您收集整理的探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息全部内容,希望文章能够帮你解决探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。