我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?
(SQL比在管理工作室的GUI中点击更可取)
我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?
(SQL比在管理工作室的GUI中点击更可取)
当前回答
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中的一个固有表名。
其他回答
我知道这是一个很晚(非常晚)的回复,但我找到了这些简单的方法来找到所有的foreign_key_references。这是解决方案;
解决方案1:
EXEC SP_FKEYS 'MyTableName'; // It'll show you the all the information(in multiple tables) regarding to the TableName with all ForeignKey_References.
解决方案2:
EXEC SP_HELP 'MyTableName'; // It'll show all ForeignKey references in a single table.
解决方案03:
// It'll show you the Column_Name with Referenced_Table_Name
SELECT
COL_NAME(fc.parent_object_id,fc.parent_column_id) Column_Name,
OBJECT_NAME(f.parent_object_id) Table_Name
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) = 'MyTableName'
希望这对你有很大帮助。: -)
上面有一些不错的答案。但我更喜欢一个问题就能得到答案。 这段代码来自sys. .Sp_helpconstraint (sys proc)
这是微软查找是否有与tbl关联的外键的方法。
--setup variables. Just change 'Customer' to tbl you want
declare @objid int,
@objname nvarchar(776)
select @objname = 'Customer'
select @objid = object_id(@objname)
if exists (select * from sys.foreign_keys where referenced_object_id = @objid)
select 'Table is referenced by foreign key' =
db_name() + '.'
+ rtrim(schema_name(ObjectProperty(parent_object_id,'schemaid')))
+ '.' + object_name(parent_object_id)
+ ': ' + object_name(object_id)
from sys.foreign_keys
where referenced_object_id = @objid
order by 1
答案看起来像这样:test_db_name.dbo。账户:FK_Account_Customer
第一个
EXEC sp_fkeys 'Table', 'Schema'
然后使用NimbleText处理你的结果
试试这个:
sp_help 'TableName'
下面是我将使用的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
这不是特别清晰的SQL,所以让我们看一个例子。
所以,假设我想删除Microsoft心爱的Northwind数据库中的Employees表,但是SQL Server告诉我一个或多个外键阻止我这样做。
上面的SQL命令将返回这些结果…
它显示有3个外键引用雇员表。换句话说,除非先删除这三个外键,否则不允许删除(删除)这个表。
在结果中,第一行是以下外键约束在结果中的显示方式。
ALTER TABLE [dbo].[Employees] WITH NOCHECK
ADD CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo])
REFERENCES [dbo].[Employees] ([EmployeeID])
倒数第二列显示了我需要使用的SQL命令来删除这些外键之一,例如:
ALTER TABLE [Employees] DROP CONSTRAINT [FK_Employees_Employees]
...右边一列显示了创建它的SQL…
ALTER TABLE [Employees] WITH NOCHECK
ADD CONSTRAINT [FK_Employees_Employees]
FOREIGN KEY([ReportsTo]) REFERENCES [Employees] ([EmployeeID])
使用所有这些命令,您就拥有了删除相关外键所需的一切,从而可以删除表,然后再重新创建它们。
唷。希望这能有所帮助。