我需要删除SQL Server数据库中高度引用的表。如何获取我需要删除的所有外键约束的列表以便删除表?
(在管理工作室的GUI中,SQL答案比点击更好。)
发布于 2019-05-31 08:14:31
原始问题要求将所有外键的列表放入高度引用的表中,以便可以删除该表。
这个小查询返回将所有外键放入特定表所需的所有“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子句以获取当前数据库中所有外键的drop命令。
发布于 2019-05-31 09:46:01
我在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')
)
https://stackoverflow.com/questions/-100001224
复制相似问题