如何在SQL Server中列出所有引用给定表的外键？

Question

如何在SQL Server中列出所有引用给定表的外键？

sqlsql-servert-sql

973

我需要在SQL Server数据库中删除一个被大量引用的表格。我该如何获取需要删除的所有外键约束列表，以便删除该表?

（最好使用SQL语句来回答，不要在管理工作室的GUI中点击）

- chillitom

请参考如何脚本化表的所有外键获取帮助。更新：链接已不再可用，但相关的SQL已经作为答案复制到了一个相关问题中。您也可以通过GUI查看依赖项。 - Galwegian

32个回答

303

这个给你:

FK 本身
FK 所属的模式
"引用表"，或者包含 FK 的表
"引用列"，或者是指向 FK 的引用表中的列
"被引用表"，或者是你的 FK 指向的关键列所在的表
"被引用列"，或者是你的 FK 指向的关键字

以下是代码:

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

- Gustavo Rubio

26

如果您想在之后筛选结果，这是我认为最好的答案。 - Faliorn

非常好！如果您能够：a）在所有列名前加上“Fk” / “Key”，b）在所有列名后缀上“Name”，c）删除下划线，d）添加KeyTableSchemaName，e）添加默认的order by：KeyTableSchemaName，KeyTableName，KeyColumnName，FkTableSchemaName，FkTableName，FkName，并且f）将列顺序更改为：KeyTableSchemaName，KeyTableName，KeyColumnName，FkTableSchemaName，FkTableName，FkName，FkColumnName，以保持一致性/最常见的最佳实践命名约定和d/e以实现最可能的使用（列出“表”的FK依赖项）。那就更好了。 - Tom

2

这是一个非常好的答案和有用的查询。谢谢。 - Rich Dominelli

如果你没有任何多列外键, 这个查询会更好地工作。 - Jirka Hanika

@cerved - 当然。我们可以在SELECT列表中包括fkc.constraint_column_id和其他模式。但这些修改可能被视为轻微的离题。对于OP自己的用例，他们更需要GROUP BY或更简单的查询来消除那些重复记录。 - Jirka Hanika

显示剩余4条评论

260

我会建议使用SQL Server Management Studio中的数据库图形化功能，但是既然您排除了这个选项 - 在SQL Server 2008中，以下方法对我有效（我没有使用2005）。

要获取引用表和列名称的列表...

select 
    t.name as TableWithForeignKey, 
    fk.constraint_column_id as FK_PartNo, c.
    name as ForeignKeyColumn 
from 
    sys.foreign_key_columns as fk
inner join 
    sys.tables as t on fk.parent_object_id = t.object_id
inner join 
    sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
where 
    fk.referenced_object_id = (select object_id 
                               from sys.tables 
                               where name = 'TableOthersForeignKeyInto')
order by 
    TableWithForeignKey, FK_PartNo

获取外键约束的名称

select distinct name from sys.objects where object_id in 
(   select fk.constraint_object_id from sys.foreign_key_columns as fk
    where fk.referenced_object_id = 
        (select object_id from sys.tables where name = 'TableOthersForeignKeyInto')
)

- Gishu

4

虽然使用了referenced_object_id而不是parent，但查询的语句很好。在sys.objects中选取唯一的名称，条件是这些对象的object_id出现在sys.foreign_key_columns表中，且这些关键字所参照的referenced_object_id等于'tablename'表的object_id。 - chillitom

5

您可以通过在第一条查询的 select 语句中添加 "object_name(constraint_object_id)" 来获取 FK 的名称。 - sam yi

3

你可以获得对象 ID object_id('TableOthersForeignKeyInto')。 - IvanH

182

试试这个：

sp_help 'TableName'

- BankZ

4

一个很好的辅助方法，可以帮助你确定是否手动浏览数据库。而且，它适用于Azure SQL Server。 - Pac0

51

您还需要注意其他对象对该表的引用。

如果其他表高度引用了该表，则其他对象（例如视图、存储过程、函数等）对其的引用也很可能很高。

我强烈建议使用GUI工具，例如SSMS中的“查看依赖关系”对话框或免费工具ApexSQL Search，因为如果您想仅使用SQL搜索依赖项，则可能会出现错误。

如果SQL是唯一选项，您可以尝试这样做。

select O.name as [Object_Name], C.text as [Object_Definition]
from sys.syscomments C
inner join sys.all_objects O ON C.id = O.object_id
where C.text like '%table_name%'

- Mspaja

24

原始问题要求获取所有指向高度引用表的外键列表，以便可以删除该表。

这个小查询将返回删除特定表中所有外键所需的所有“drop foreign key”命令：

SELECT 
   'ALTER TABLE ['+sch.name+'].['+referencingTable.Name+'] DROP CONSTRAINT ['+foreignKey.name+']' '[DropCommand]'
FROM sys.foreign_key_columns fk
    JOIN sys.tables referencingTable ON fk.parent_object_id = referencingTable.object_id
    JOIN sys.schemas sch ON referencingTable.schema_id = sch.schema_id
    JOIN sys.objects foreignKey ON foreignKey.object_id = fk.constraint_object_id
    JOIN sys.tables referencedTable ON fk.referenced_object_id = referencedTable.object_id
WHERE referencedTable.name = 'MyTableName'

示例输出：

[DropCommand]
ALTER TABLE [dbo].[OtherTable1] DROP CONSTRAINT [FK_OtherTable1_MyTable]
ALTER TABLE [dbo].[OtherTable2] DROP CONSTRAINT [FK_OtherTable2_MyTable]

省略WHERE子句以获取当前数据库中所有外键的删除命令。

- J S

你能否为这个代码添加一些解释，说明它的作用和工作原理？ - FreelanceConsultant

不支持Oracle。 - FreelanceConsultant

24

最简单的方法是使用SQL中的sys.foreign_keys_columns。表格包含了所有外键的对象ID以及它们关联的列ID，参考表ID以及引用列和表。由于ID保持不变，因此结果在进一步修改模式和表时将是可靠的。

查询：

SELECT    
OBJECT_NAME(fkeys.constraint_object_id) foreign_key_name
,OBJECT_NAME(fkeys.parent_object_id) referencing_table_name
,COL_NAME(fkeys.parent_object_id, fkeys.parent_column_id) referencing_column_name
,OBJECT_SCHEMA_NAME(fkeys.parent_object_id) referencing_schema_name
,OBJECT_NAME (fkeys.referenced_object_id) referenced_table_name
,COL_NAME(fkeys.referenced_object_id, fkeys.referenced_column_id) 
referenced_column_name
,OBJECT_SCHEMA_NAME(fkeys.referenced_object_id) referenced_schema_name
FROM sys.foreign_key_columns AS fkeys

我们也可以使用“where”来添加过滤器

WHERE OBJECT_NAME(fkeys.parent_object_id) = 'table_name' AND 
OBJECT_SCHEMA_NAME(fkeys.parent_object_id) = 'schema_name'

- Garuda prasad K

这非常适用于需要删除整个数据库结构/一组引用表的情况。 - Morvael

24

以下是我会使用的SQL代码。

SELECT 
   f.name AS 'Name of Foreign Key',
   OBJECT_NAME(f.parent_object_id) AS 'Table name',
   COL_NAME(fc.parent_object_id,fc.parent_column_id) AS 'Fieldname',
   OBJECT_NAME(t.object_id) AS 'References Table name',
   COL_NAME(t.object_id,fc.referenced_column_id) AS 'References fieldname',

   'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id) + ']  DROP CONSTRAINT [' + f.name + ']' AS 'Delete foreign key',

   'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id) + ']  WITH NOCHECK ADD CONSTRAINT [' + 
        f.name + '] FOREIGN KEY([' + COL_NAME(fc.parent_object_id,fc.parent_column_id) + ']) REFERENCES ' + 
        '[' + OBJECT_NAME(t.object_id) + '] ([' +
        COL_NAME(t.object_id,fc.referenced_column_id) + '])' AS 'Create foreign key'
    -- , delete_referential_action_desc AS 'UsesCascadeDelete'
FROM sys.foreign_keys AS f,
     sys.foreign_key_columns AS fc,
     sys.tables t 
WHERE f.OBJECT_ID = fc.constraint_object_id
AND t.OBJECT_ID = fc.referenced_object_id
AND OBJECT_NAME(t.object_id) = 'Employees'      --  Just show the FKs which reference a particular table
ORDER BY 2

这段内容涉及到IT技术，讲解SQL语言。如果我想在微软喜爱的Northwind数据库中删除Employees表，但是SQL Server告诉我有一个或多个外键阻止了我这样做，那么下面的SQL命令将返回以下结果...

它告诉我有3个外键引用了Employees表。换句话说，在删除这个表之前，必须先删除这三个外键。

结果中，第一行是如何显示以下外键约束的结果。

ALTER TABLE [dbo].[Employees]  WITH NOCHECK 
ADD CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo])
REFERENCES [dbo].[Employees] ([EmployeeID])

第二列显示了我需要使用的SQL命令来删除其中一个外键，例如：

ALTER TABLE [Employees] DROP CONSTRAINT [FK_Employees_Employees]

...右侧列显示创建它的SQL语句...

ALTER TABLE [Employees] WITH NOCHECK 
ADD CONSTRAINT [FK_Employees_Employees] 
FOREIGN KEY([ReportsTo]) REFERENCES [Employees] ([EmployeeID])

使用这些命令，你可以删除相关的外键以允许你删除一个表，然后稍后重新创建它们。

哎呀，希望这能帮到你。

- Mike Gledhill

如果您使用内连接和ON子句而不是交叉连接，那么会更清晰明了。但是这仍然很有帮助！ - TamusJRoyce

我扩展了CreateForeignKey并添加了： ' + ON DELETE ' + (REPLACE(delete_referential_action_desc, '', ' ') collate SQL_Latin1_General_CP1_CI_AS) + ' ON UPDATE ' + (REPLACE(update_referential_action_desc, '', ' ') collate SQL_Latin1_General_CP1_CI_AS) AS 'CreateForeignKeyScript' - undefined

18

SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),
       PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME),
       PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME),
       FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),
       FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME),
       FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME),
       -- Force the column to be non-nullable (see SQL BU 325751)
       --KEY_SEQ             = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),
       UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade') 
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade') 
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),
       PK_NAME = CONVERT(SYSNAME,I.NAME),
       DEFERRABILITY = CONVERT(SMALLINT,7)   -- SQL_NOT_DEFERRABLE
FROM   SYS.ALL_OBJECTS O1,
       SYS.ALL_OBJECTS O2,
       SYS.ALL_COLUMNS C1,
       SYS.ALL_COLUMNS C2,
       SYS.FOREIGN_KEYS F
       INNER JOIN SYS.FOREIGN_KEY_COLUMNS K
         ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)
       INNER JOIN SYS.INDEXES I
         ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID
             AND F.KEY_INDEX_ID = I.INDEX_ID)
WHERE  O1.OBJECT_ID = F.REFERENCED_OBJECT_ID
       AND O2.OBJECT_ID = F.PARENT_OBJECT_ID
       AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID
       AND C2.OBJECT_ID = F.PARENT_OBJECT_ID
       AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID
       AND C2.COLUMN_ID = K.PARENT_COLUMN_ID

- Omu

17

SELECT
  object_name(parent_object_id),
  object_name(referenced_object_id),
  name 
FROM sys.foreign_keys
WHERE parent_object_id = object_id('Table Name')

- Vishal Gajjar

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Recep · Accepted Answer

不确定为什么没有人提出建议，但我使用 sp_fkeys 查询给定表的外键：

EXEC sp_fkeys 'TableName'

你也可以指定模式（schema）：

EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'

在未指定模式的情况下，文档中说明如下：

如果未指定pktable_owner，则使用底层DBMS的默认表可见性规则。

在SQL Server中，如果当前用户拥有指定名称的表，则返回该表的列。如果未指定pktable_owner且当前用户没有拥有指定pktable_name的表，则该过程将查找由数据库所有者拥有的具有指定pktable_name的表。如果存在这样的表，则返回该表的列。