如何删除SQL Server表的列表，而忽略约束? [英] How to drop a list of SQL Server tables, ignoring constraints?

问题描述

我有一个要从数据库中立即删除的六个MSSQL 2008表的列表.数据已完全迁移到新表. new 表中没有对 old 表的引用.

I have a list of half a dozen MSSQL 2008 tables that I would like to remove at once from my database. The data has been entirely migrated to new tables. There is no reference in the new tables to the old tables.

问题在于旧表附带了由工具自动生成的内部FK约束的负载(实际上是aspnet_regsql).因此，手动放弃所有约束确实是一个痛苦.

The problem being that old tables comes with loads of inner FK constraints that have been autogenerated by a tool (aspnet_regsql actually). Hence dropping manually all constraints is a real pain.

如何删除旧表而忽略所有内部约束?

How can I can drop the old tables ignoring all inner constraints?

推荐答案

这取决于您要删除表的方式.如果需要删除表列表，请覆盖数据库中几乎超过20％的表.

It depends on how you want to drop the tables. If list of tables need to drop covers almost above 20 % of tables under your DB.

然后，我将在脚本下禁用该数据库中的所有约束，并删除表并在同一脚本下启用约束.

Then I will disable all the constraints in that DB under my script and drop the tables and Enable the constraints under the same script.

--To Disable a Constraint at DB level

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

--Write the code to DROP tables

DROP TABLE TABLENAME

DROP TABLE TABLENAME

DROP TABLE TABLENAME

--To Enable a Constraint at DB level

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

最后检查约束状态，启动该查询.

Finally to check the Status of your constraints fire up this Query.

--Checks the Status of Constraints

SELECT (CASE 
    WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
    ELSE 'DISABLED'
    END) AS STATUS,
    OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
    OBJECT_NAME(FKEYID) AS TABLE_NAME,
    COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
    OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
    COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO

如果您不想在数据库级别禁用约束，请列出要删除的表的列表.

If you dont want to disable the constraints at Database level then make a list of tables which you want to drop.

第1步:检查与thos表关联的约束

Step1 : Check the Constraints associated with thos tables

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('dbo.Tablename')

第2步:禁用与这些表关联的约束.

Step2 : Disable the Constraints which are associated with these tables.

ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

Step3:删除表格

Step3 : Drop the tables

DROP TABLE TABLENAME

这篇关于如何删除SQL Server表的列表，而忽略约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！