如何查找在SQL Server中引用表的外键约束?

I am trying to drop a table but getting the following message:

我正在尝试删除一个表，但得到以下信息:

Msg 3726, Level 16, State 1, Line 3
Could not drop object 'dbo.UserProfile' because it is referenced by a FOREIGN KEY constraint.
Msg 2714, Level 16, State 6, Line 2
There is already an object named 'UserProfile' in the database.

Msg 3726, 16级，状态1，第3行不能删除对象的dbo。因为它被外键约束引用。在数据库中已经有一个名为“UserProfile”的对象。

I looked around with SQL Server Management Studio but I am unable to find the constraint. How can I find out the foreign key constraints?

我查看了SQL Server Management Studio，但我无法找到约束。如何找出外键约束?

14 个解决方案

#1

Here it is:

这里是:

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'

This way, you'll get the referencing table and column name.

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

Edited to use sys.tables instead of generic sys.objects as per comment suggestion. Thanks, marc_s

编辑使用系统。表而不是通用系统。对象根据注释建议。谢谢,marc_s

#2

Try this

试试这个

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

#3

Another way is to check the results of

另一种方法是检查结果

sp_help 'TableName'

(or just highlight the quoted TableName and pres ALT+F1)

(或者只突出显示引用的TableName和pres ALT+F1)

With time passing, I just decided to refine my answer. Below is a screenshot of the results that sp_help provides. A have used the AdventureWorksDW2012 DB for this example. There is numerous good information there, and what we are looking for is at the very end - highlighted in green:

随着时间的流逝，我决定完善我的答案。下面是sp_help提供的结果的屏幕截图。A在这个示例中使用了AdventureWorksDW2012 DB。这里有大量的好信息，我们正在寻找的是在最后用绿色强调的:

#4

I found this answer quite simple and did the trick for what I needed: https://*.com/a/12956348/652519

我发现这个答案非常简单，并且实现了我所需要的:https://*.com/a/1295638/652519

A summary from the link, use this query:

来自链接的摘要，使用此查询:

EXEC sp_fkeys 'TableName'

Quick and simple. I was able to locate all the foreign key tables, respective columns and foreign key names of 15 tables pretty quickly.

快速和简单。我很快就找到了15个表的所有外键表、各自的列和外键名。

As @mdisibio noted below, here's a link to the documentation that details the different parameters that can be used: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys-transact-sql

正如下面@mdisibio所指出的，这里有一个指向文档的链接，详细说明可以使用的不同参数:https://docs.microsoft.com/en- us/sql/relationaldatabases/system - stordures/sp -fkey -transact-sql

#5

I am using this script to find all details related to foreign key. I am using INFORMATION.SCHEMA. Below is a SQL Script:

我使用这个脚本查找与外键相关的所有细节。我用INFORMATION.SCHEMA。下面是一个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

#6

if you want to go via SSMS on the object explorer window, right click on the object you want to drop, do view dependencies.

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

#7

Here is the best way to find out Foreign Key Relationship in all Database.

这是在所有数据库中找出外键关系的最佳方法。

exec sp_helpconstraint 'Table Name'

and one more way

还有一个方法

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

#8

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;

#9

--The following may give you more of what you're looking for:

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

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

#10

You can also return all the information about the Foreign Keys by adapating @LittleSweetSeas answer:

您还可以通过修改@LittleSweetSeas回答返回所有关于外键的信息:

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

#11

try the following query.

尝试下面的查询。

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

this will give the constraint_name, column_names which will be referring and tables which will be depending on the constraint will be there.

这将给出constraint_name、将要引用的column_names以及依赖于约束的表。

#12

You could use this query to display Foreign key constaraints:

您可以使用此查询来显示外键约束:

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'

Taken from 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/

从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/

#13

The easiest way to get Primary Key and Foreign Key for a table is:

获取表的主键和外键最简单的方法是:

/*  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'

#14

In SQL Server Management Studio you can just right click the table in the object explorer and select "View Dependencies". This would give a you a good starting point. It shows tables, views, and procedures that reference the table.

在SQL Server Management Studio中，您只需右键单击object explorer中的表并选择“视图依赖项”。这会给你一个很好的起点。它显示引用该表的表、视图和过程。