如何查找引用 SQLServer 中表的 FOREIGNKEY 约束？

小开

如果您想通过对象资源管理器窗口上的 SSMS 访问，右键单击您想要删除的对象，执行视图依赖关系。

小开

最佳答案

这就是:

SELECT
OBJECT_NAME(f.parent_object_id) TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM
sys.foreign_keys AS f
INNER JOIN
sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN
sys.tables t
ON t.OBJECT_ID = fc.referenced_object_id
WHERE
OBJECT_NAME (f.referenced_object_id) = 'YourTableName'

这样，您将获得引用表和列名。

根据注释建议编辑为使用 sys.tables 而不是通用 sys.object。谢谢 Marc _ s

小开

试试这个

SELECT
object_name(parent_object_id) ParentTableName,
object_name(referenced_object_id) RefTableName,
name
FROM sys.foreign_keys
WHERE parent_object_id = object_id('Tablename')

小开

以下是查找所有数据库中的外键关系的最佳方法。

exec sp_helpconstraint 'Table Name'

还有一个办法

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME='Table Name'
--and left(CONSTRAINT_NAME,2)='FK'(If you want single key)

小开

另一种方法是检查

sp_help 'TableName'

(或者只需突出显示引用的 TableName 并按 ALT + F1)

随着时间的推移，我决定改进我的答案。下面是 sp_help提供的结果的屏幕截图。A 为此示例使用了 AdventureWorksDW2012DB。那里有许多好的信息，我们正在寻找的是在最后用绿色高亮显示的:

小开

——以下可能会给你更多你想要的东西:

create Procedure spShowRelationShips
(
@Table varchar(250) = null,
@RelatedTable varchar(250) = null
)
as
begin
if @Table is null and @RelatedTable is null
select  object_name(k.constraint_object_id) ForeginKeyName,
object_name(k.Parent_Object_id) TableName,
object_name(k.referenced_object_id) RelatedTable,
c.Name RelatedColumnName,
object_name(rc.object_id) + '.' + rc.name RelatedKeyField
from sys.foreign_key_columns k
left join sys.columns c on object_name(c.object_id) = object_name(k.Parent_Object_id) and c.column_id = k.parent_column_id
left join sys.columns rc on object_name(rc.object_id) = object_name(k.referenced_object_id) and rc.column_id = k.referenced_column_id
order by 2,3


if @Table is not null and @RelatedTable is null
select  object_name(k.constraint_object_id) ForeginKeyName,
object_name(k.Parent_Object_id) TableName,
object_name(k.referenced_object_id) RelatedTable,
c.Name RelatedColumnName,
object_name(rc.object_id) + '.' + rc.name RelatedKeyField
from sys.foreign_key_columns k
left join sys.columns c on object_name(c.object_id) = object_name(k.Parent_Object_id) and c.column_id = k.parent_column_id
left join sys.columns rc on object_name(rc.object_id) = object_name(k.referenced_object_id) and rc.column_id = k.referenced_column_id
where object_name(k.Parent_Object_id) =@Table
order by 2,3


if @Table is null and @RelatedTable is not null
select  object_name(k.constraint_object_id) ForeginKeyName,
object_name(k.Parent_Object_id) TableName,
object_name(k.referenced_object_id) RelatedTable,
c.Name RelatedColumnName,
object_name(rc.object_id) + '.' + rc.name RelatedKeyField
from sys.foreign_key_columns k
left join sys.columns c on object_name(c.object_id) = object_name(k.Parent_Object_id) and c.column_id = k.parent_column_id
left join sys.columns rc on object_name(rc.object_id) = object_name(k.referenced_object_id) and rc.column_id = k.referenced_column_id
where object_name(k.referenced_object_id) =@RelatedTable
order by 2,3






end

小开

我发现这个答案非常简单，并且按照我的需要做到了: https://stackoverflow.com/a/12956348/652519

来自链接的摘要，请使用以下查询:

EXEC sp_fkeys 'TableName'

简单明了。我能够很快地找到15个表的所有外键表、各自的列和外键名。

如下面@mdisibio 所指出的，这里有一个文档链接，详细介绍了可以使用的不同参数: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys-transact-sql

小开

尝试下面的查询。

select object_name(sfc.constraint_object_id) AS constraint_name,
OBJECT_Name(parent_object_id) AS table_name ,
ac1.name as table_column_name,
OBJECT_name(referenced_object_id) as reference_table_name,
ac2.name as reference_column_name
from  sys.foreign_key_columns sfc
join sys.all_columns ac1 on (ac1.object_id=sfc.parent_object_id and ac1.column_id=sfc.parent_column_id)
join sys.all_columns ac2 on (ac2.object_id=sfc.referenced_object_id and ac2.column_id=sfc.referenced_column_id)
where sfc.parent_object_id=OBJECT_ID(<main table name>);

这将给出约束 _ name、将引用的 column _ name 和将依赖于约束的表。

小开

我正在使用这个脚本来查找与外键相关的所有细节。我正在使用信息。下面是一个 SQL 脚本:

SELECT
ccu.table_name AS SourceTable
,ccu.constraint_name AS SourceConstraint
,ccu.column_name AS SourceColumn
,kcu.table_name AS TargetTable
,kcu.column_name AS TargetColumn
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
ON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON kcu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
ORDER BY ccu.table_name

小开

您可以使用这个查询来显示 Foreign key约束:

SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
ORDER BY
1,2,3,4
WHERE PK.TABLE_NAME='YourTable'

取自 http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/

小开

你也可以通过改编@LittleSweetSea 来返回关于 Foreign Keys的所有信息:

SELECT
OBJECT_NAME(f.parent_object_id) ConsTable,
OBJECT_NAME (f.referenced_object_id) refTable,
COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM
sys.foreign_keys AS f
INNER JOIN
sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN
sys.tables t
ON t.OBJECT_ID = fc.referenced_object_id
order by
ConsTable

小开

SELECT
obj.name      AS FK_NAME,
sch.name      AS [schema_name],
tab1.name     AS [table],
col1.name     AS [column],
tab2.name     AS [referenced_table],
col2.name     AS [referenced_column]
FROM
sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id
AND col2.object_id =  tab2.object_id;

小开

获得表的 Primary Key和 Foreign Key的最简单方法是:

/*  Get primary key and foreign key for a table */
USE DatabaseName;


SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME LIKE 'PK%' AND
TABLE_NAME = 'TableName'


SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME LIKE 'FK%' AND
TABLE_NAME = 'TableName'

小开

在 SQLServerManagementStudio 中，只需右键单击对象资源管理器，并选择“查看依赖项” 很好的起点。它显示了引用桌子。

小开

在对象资源管理器中，展开表并展开键:

小开

手术

_sp_help 'tbl_name'_

确实提供了很多信息，但我发现程序

_sp_fkeys 'tbl_name'_ and
_sp_pkeys 'tbl_name'_

更容易使用，而且可能有一个更加防止未来的结果。

(他们确实完美地回答了观测点)