mssql 使用外键名称查看外键列的信息的方法分享


摘要:
下文讲述使用外键名称获取外键所属列的方法分享,如下所示
实验环境:sql server 2008 R2

实现思路:
通过查询 系统表sys.foreign_keys 和 sys.foreign_key_columns 获取外键所属列

select fk.object_id as FK_Object_ID,
    fk.name as FK_name,
    pt.name as ParentTable_Name,
    pc.name as ParentTable_Column_Name,
    rt.name as ReferencedTable_Name,
    rc.name as ReferencedTable_Column_Name
from sys.foreign_keys fk with(nolock)
inner join sys.foreign_key_columns fkc with(nolock)
    on fk.object_id=fkc.constraint_object_id
inner join sys.tables pt with(nolock)
    on fkc.parent_object_id=pt.object_id
inner join sys.columns pc with(nolock)
    on fkc.parent_object_id=pc.object_id 
        and fkc.parent_column_id=pc.column_id
inner join sys.tables as rt with(nolock)
    on fkc.referenced_object_id=rt.object_id
inner join sys.columns rc with(nolock)
    on fkc.referenced_object_id=rc.object_id 
        and fkc.referenced_column_id=rc.column_id
		where rt.name=N'外键名称'