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

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

（在管理工作室的GUI中点击SQL时最好选择SQL。）

---

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

EXEC sp_fkeys 'TableName'

---

我会使用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')
)

---

这给你：

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

链接地址: http://www.djcxy.com/p/16893.html

上一篇: How can I list all foreign keys referencing a given table in SQL Server?

下一篇: DateTime2 vs DateTime in SQL Server