我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?
(SQL比在管理工作室的GUI中点击更可取)
我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?
(SQL比在管理工作室的GUI中点击更可取)
当前回答
以下是我认为在SQL Server 2016中处理这种情况的最佳实践。
你必须列出外键使用:
EXEC sp_fkeys 'TableName'
在这里你可以看到fk的全部信息。注意列FKTABLE_NAME, FKCOLUMN_NAME, FK_NAME, UPDATE_RULE, DELETE_RULE是你需要删除外键并在截断后再次实现它们的信息。
你可以组织一个脚本如下:
-- EXEC sp_fkeys 'TableName'
-- DROP CONSTRAINTS: I drop one, here drop every constraint you desire.
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TableName
DROP CONSTRAINT IF EXISTS FK_TableName_OtherTable
GO
ALTER TABLE dbo.TableName SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
-- TRUNCATE
BEGIN TRANSACTION
TRUNCATE TABLE TableName
GO
COMMIT
-- RECREATE CONSTRAINTS: I recreate 1, here recreate every fk you desire
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TableName SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.TableName ADD CONSTRAINT
FK_TableName_OtherTable FOREIGN KEY
(
Id_FK
) REFERENCES dbo.OtherTable
(
Id
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
COMMIT
** UPDATE_RULE和DELETE_RULE的值可以在sp_fkeys的文档中看到:
其他回答
这将获得涉及所选表的任何外键。*假设为_FIRSTABLENAME_SECONDTABLENAME格式。
declare @tablename as varchar(MAX)
SET @tablename = 'yourtablename'
SELECT name
FROM YOURDATABASE.sys.objects
WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT' and (name LIKE '%_' + @tablename + 'empdb_%' or name LIKE '%_' + @tablename )
这是更一般的形式:
SELECT name
FROM YOURDATABASE_PROD.sys.objects
WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT' and name LIKE '%' + @tablename + '%' and
name NOT LIKE '[a-zA-Z0-9]' + @tablename + '%' and name NOT LIKE '%' + @tablename + '[a-zA-Z0-9]'
不知道为什么没有人建议,但我使用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。如果存在,那张桌子就是 返回列。
上面有一些不错的答案。但我更喜欢一个问题就能得到答案。 这段代码来自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
@BankZ的最好回答
sp_help 'TableName'
另外,对于不同的模式
sp_help 'schemaName.TableName'
试试这个:
sp_help 'TableName'