我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?
(SQL比在管理工作室的GUI中点击更可取)
我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?
(SQL比在管理工作室的GUI中点击更可取)
当前回答
我将使用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')
)
其他回答
试试这个:
sp_help 'TableName'
Oracle SQL
select *
from
all_constraints
where
r_constraint_name in
(select constraint_name
from
all_constraints
where
table_name='PUT_THE_TABLE_NAME_HERE');
all_constraints是Oracle DB中的一个固有表名。
我将使用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')
)
最初的问题要求将所有外键的列表放入一个高度引用的表中,以便可以删除表。
这个小查询返回将所有外键放入特定表所需的“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 -子句以获取当前数据库中所有外键的删除命令。
不知道为什么没有人建议,但我使用sp_fkeys查询给定表的外键:
EXEC sp_fkeys 'TableName'
你也可以指定模式:
EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'
在没有指定模式的情况下,文档声明如下:
如果没有指定pktable_owner,则默认表可见性规则 的基础DBMS应用程序。 在SQL Server中,如果当前用户拥有一个具有指定 Name,返回该表的列。如果pktable_owner不是 且当前用户不拥有具有指定属性的表 Pktable_name时,该过程查找具有指定 由数据库所有者拥有的Pktable_name。如果存在,那张桌子就是 返回列。