如何删除 SQL Server 表列表，忽略约束？

Question

我有一个包含六个 MSSQL 2008 表的列表，我想立即从数据库中删除它们。数据已完全迁移到新表。 新表中没有对旧表的引用。

问题是旧表带有大量由工具（实际上是 aspnet_regsql）自动生成的内部 FK 约束。因此，手动删除所有约束是一件非常痛苦的事情。

我怎样才能删除旧表而忽略所有内部约束？

Answer 1

这取决于您想要如何删除表。如果需要删除的表列表几乎涵盖了数据库下 20% 以上的表。

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

--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'

最后，要检查约束的状态，请启动此查询。

--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

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

步骤 1：检查与这些表关联的约束 步骤

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

2：禁用与这些表关联的约束。

ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

Step3：删除表

DROP TABLE TABLENAME

Answer 2

一个简单的 删除表 dbo.MyTable将忽略除外键之外的所有约束（和触发器）（除非您首先删除子表/引用表），您可能必须先删除它们。

编辑：评论后：

没有自动的方法。您必须迭代 sys.foreign_keys< /a> 并生成一些 ALTER TABLE 语句。

Answer 3

执行以下脚本，删除当前DB下所有表的所有约束，然后执行drop table语句。

DECLARE @dropAllConstraints NVARCHAR(MAX) = N'';

SELECT @dropAllConstraints  += N'
ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))
    + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + 
    ' DROP CONSTRAINT ' + QUOTENAME(name) + ';'
FROM sys.foreign_keys;
EXEC sp_executesql @dropAllConstraints 

Answer 4

我找到了一种合理的方法来做到这一点，即让 SQL 编写 SQL 来删除约束：

select concat("alter table ", table_name, " drop ", constraint_type ," ", constraint_name, ";")
  from information_schema.table_constraints 
  where table_name like 'somefoo_%' 
        and 
        constraint_type <> "PRIMARY KEY";

您将需要修改表名以满足您的需要，或者可能选择其他列/值。

此外，这将选择任何非主键约束，这可能太大了。也许您需要将其设置为=？

我不是 DBA。可能有更好的方法来做到这一点，但它对于我的目的来说已经足够好了。

Answer 5

我终于找到了基于 脚本由 Jason Presley 提供。该脚本会自动删除数据库中的所有约束。添加 WHERE 子句很容易，以便它仅适用于相关表集。之后，删除所有表就很简单了。

Answer 6

请务必小心以下脚本、数据库中的所有表、视图、函数、存储过程和用户​​定义类型，忽略所有约束。

/*
  Description: This script will remove all tables, views, functions, stored procedures and user defined types from a database.
*/
        declare @n char(1)
        set @n = char(10)

        declare @stmt nvarchar(max)

        -- procedures
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop procedure [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.procedures


        -- check constraints
        select @stmt = isnull( @stmt + @n, '' ) +
        'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + ']    drop constraint [' + name + ']'
        from sys.check_constraints

        -- functions
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop function [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.objects
        where type in ( 'FN', 'IF', 'TF' )

        -- views
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop view [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.views

        -- foreign keys
        select @stmt = isnull( @stmt + @n, '' ) +
        'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] drop constraint [' + name + ']'
        from sys.foreign_keys

        -- tables
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop table [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.tables

        -- user defined types
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop type [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.types
        where is_user_defined = 1


        exec sp_executesql @stmt

Answer 7

我怀疑您必须在删除之前对有问题的表执行“alter”命令才能删除外来键约束。

ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders;
DROP TABLE Orders;

当然，如果你先删除子表，那么就不会出现这个问题。
（除非您有表 A 对表 B 的约束，表 B 对 A 的约束，那么您将需要更改其中一个表，例如 A 以删除约束）

例如，这不起作用，因为订单具有来自 Order_Lines 的约束，

DROP TABLE Orders;
DROP TABLE Order_lines;

例如这将工作

DROP TABLE Order_lines;
DROP TABLE Orders;