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

How can I list all foreign keys referencing a given table in SQL Server?

提问by chillitom

I need to remove a highly referenced table in a SQL Server database. How can I get a list of all the foreign key constraints I will need to remove in order to drop the table?

我需要删除 SQL Server 数据库中高度引用的表。如何获取需要删除以删除表的所有外键约束的列表？

(SQL answers preferable over clicking about in the GUI of the management studio.)

（SQL 答案比在管理工作室的 GUI 中单击更可取。）

回答by Recep

Not sure why no one suggested but I use sp_fkeysto query foreign keys for a given table:

不知道为什么没有人建议，但我sp_fkeys用来查询给定表的外键：

EXEC sp_fkeys 'TableName'

You can also specify the schema:

您还可以指定架构：

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

Without specifying the schema, the docsstate the following:

在没有指定架构的情况下，文档声明如下：

If pktable_owner is not specified, the default table visibility rules of the underlying DBMS apply.

In SQL Server, if the current user owns a table with the specified name, that table's columns are returned. If pktable_owner is not specified and the current user does not own a table with the specified pktable_name, the procedure looks for a table with the specified pktable_name owned by the database owner. If one exists, that table's columns are returned.

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

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

回答by Gishu

I'd use the Database Diagramming feature in SQL Server Management Studio, but since you ruled that out - this worked for me in SQL Server 2008 (don't have 2005).

我会使用 SQL Server Management Studio 中的数据库图表功能，但由于您排除了这一点 - 这在 SQL Server 2008（没有 2005）中对我有用。

To get list of referring table and column names...

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

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

To get names of foreign key constraints

获取外键约束的名称

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')
)

回答by Gustavo Rubio

This gives you:

这给你：

• The FK itself itself
• Schema that the FK belongs to
• The "referencing table" or the table that has the FK
• The "referencing column" or the column inside referencing table that points to the FK
• The "referenced table" or the table that has the key column that your FK is pointing to
• The "referenced column" or the column that is the key that your FK is pointing to

• FK 本身
• FK所属的Schema
• “引用表”或具有 FK 的表
• “引用列”或引用表中指向 FK 的列
• “引用表”或具有 FK 指向的键列的表
• “引用列”或作为您的 FK 指向的键的列

Code below:

代码如下：

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

回答by BankZ

Try this :

尝试这个 ：

sp_help 'TableName'

回答by Mspaja

You should also mind the references to other objects.

您还应该注意对其他对象的引用。

If the table was highly referenced by other tables than it's probably also highly referenced by other objects such as views, stored procedures, functions and more.

如果该表被其他表高度引用，那么它可能也被其他对象（如视图、存储过程、函数等）高度引用。

I'd really recommend GUI tool such as ‘view dependencies' dialog in SSMS or free tool like ApexSQL Searchfor this because searching for dependencies in other objects can be error prone if you want to do it only with SQL.

我真的建议使用 GUI 工具，例如 SSMS 中的“查看依赖项”对话框或ApexSQL Search 之类的免费工具，因为如果您只想使用 SQL 搜索其他对象中的依赖项，则很容易出错。

If SQL is the only option you could try doing it like this.

如果 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%'

回答by J S

The original question asked to get a list of all foreign keys into a highly referenced table so that the table can be removed.

最初的问题要求将所有外键的列表放入一个高度引用的表中，以便可以删除该表。

This little query returns all the 'drop foreign key' commands needed to drop all foreign keys into a particular table:

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

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'

Example output:

示例输出：

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

Omit the WHERE-clause to get the drop commands for all foreign keys in the current database.

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

回答by Mike Gledhill

Here's the SQL code I would use.

这是我将使用的 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's not particularly clear SQL, so let's look at an example.

不是特别清楚的SQL，我们来看一个例子。

So, supposing I wanted to drop the Employeestable in Microsoft's beloved Northwinddatabase, but SQL Server told me that one or more Foreign Keys were preventing me from doing this.

因此，假设我想删除EmployeesMicrosoft 钟爱的Northwind数据库中的表，但 SQL Server 告诉我一个或多个外键阻止我这样做。

The SQL command above would return these results...

上面的 SQL 命令将返回这些结果...

It shows me that there are 3 Foreign Keys which reference the Employeestable. In other words, I wouldn't be allowed to delete (drop) this table until these three Foreign Keys are first deleted.

它告诉我有 3 个外键引用了该Employees表。换句话说，在这三个外键被首先删除之前，我不会被允许删除（删除）这个表。

In the results, the first row is how the following Foreign Key constraint would be shown in the results.

在结果中，第一行是以下外键约束在结果中的显示方式。

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

The second-to-last column shows the SQL command I would need to use to deleteone of these Foreign Keys, eg:

倒数第二列显示了我需要用来删除这些外键之一的 SQL 命令，例如：

ALTER TABLE [Employees] DROP CONSTRAINT [FK_Employees_Employees]

...and the right-hand column shows the SQL to createit...

...右侧列显示了创建它的 SQL ...

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

With all of these commands, you have everything you need to delete the relevant Foreign Keys to allow you to delete a table, then recreate them later.

使用所有这些命令，您拥有删除相关外键所需的一切，以允许您删除表，然后稍后重新创建它们。

Phew. Hope this helps.

呼。希望这可以帮助。

回答by Omu

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

回答by Garuda Prasad K

The most Simplest one is by using sys.foreign_keys_columns in SQL. Here the table contains the Object ids of all the foreign keys wrt their Referenced column ID Referenced Table ID as well as the Referencing Columns and Tables. As the Id's remains constant the result will be reliable for further modifications in Schema as well as tables.

最简单的一种是在 SQL 中使用 sys.foreign_keys_columns。这里的表包含所有外键的对象 ID，它们的引用列 ID 引用表 ID 以及引用列和表。由于 Id 保持不变，因此对于 Schema 和表中的进一步修改，结果将是可靠的。

Query:

询问：

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

We can also add filter by using 'where'

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

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

回答by Vishal Gajjar

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