检索数据库主键和外键列表的两种方法-sqlserver-mssql


摘要:
下文将展示两张检索(查询)数据库主键和外键的方法分享


 ----方法1:使用INFORMATION_SCHEMA表获取
SELECT
DISTINCT
Constraint_Name AS [键名称],
Table_Schema AS [表架构],
Table_Name AS [表名称]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
GO
---方法2:使用sys.objects表获取
SELECT OBJECT_NAME(OBJECT_ID) AS [键名称],
SCHEMA_NAME(schema_id) AS [架构名称],
OBJECT_NAME(parent_object_id) AS [表名称],
type_desc AS [键类型]
FROM sys.objects 
WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT')
GO

当我们使用方法1的时候,无法区别键是主键还是外键,即在获取一个数据的主外键的列表时,推荐使用方法2 获取主键外数据列表