主页 / dba / 问题 / 90033
Accepted
Aaron Bertrand
Asked: 2015-01-24 10:08:51 +0800 CST

如何从所有表中删除所有约束？

  • 772

我想从 SQL Server 数据库中的所有表中删除所有默认约束、检查约束、唯一约束、主键和外键。我知道如何从中获取所有约束名称sys.objects，但是如何填充该ALTER TABLE部分？

sql-server ddl

2 个回答

  1. Best Answer

    sys.tables.object_id = sys.objects.parent_object_id您可以通过加入这些对象类型轻松获取此信息。

    DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'
  ALTER TABLE ' + QUOTENAME(s.name) + N'.'
  + QUOTENAME(t.name) + N' DROP CONSTRAINT '
  + QUOTENAME(c.name) + ';'
FROM sys.objects AS c
INNER JOIN sys.tables AS t
ON c.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s 
ON t.[schema_id] = s.[schema_id]
WHERE c.[type] IN ('D','C','F','PK','UQ')
ORDER BY c.[type];

PRINT @sql;
--EXEC sys.sp_executesql @sql;

    PRINT只是用于目测，而不是用于复制和粘贴输出（如果您想运行它，这就是注释掉的EXEC用途）-如果您有很多限制，它可能不会显示整个脚本，因为PRINT仅限于4,000 个字符 (8kb)。在这些情况下，如果您需要验证整个脚本，请参阅此提示以了解在运行之前验证脚本的其他方法。例如：

    SELECT CONVERT(xml, @sql);

    对输出感到满意后，取消注释EXEC.

    • 45

  2. 我从接受的答案开始，并修改了结构以使用 while 循环，而不是在动态 sql 中构建完整的 sql 语句。我更喜欢这个有几个原因。

    查询未存储在大 @sql 变量中。此实现允许在输出中删除用于记录目的的每个约束的打印。在我的单元测试中，执行似乎快了一点。

    Set NoCount ON

Declare @schemaName varchar(200)
set @schemaName=''
Declare @constraintName varchar(200)
set @constraintName=''
Declare @tableName varchar(200)
set @tableName=''

While exists
(   
    SELECT c.name
    FROM sys.objects AS c
    INNER JOIN sys.tables AS t
    ON c.parent_object_id = t.[object_id]
    INNER JOIN sys.schemas AS s 
    ON t.[schema_id] = s.[schema_id]
    WHERE c.[type] IN ('D','C','F','PK','UQ')
    and t.[name] NOT IN ('__RefactorLog', 'sysdiagrams')
    and c.name > @constraintName
)

Begin   
    -- First get the Constraint
    SELECT 
        @constraintName=min(c.name)
    FROM sys.objects AS c
    INNER JOIN sys.tables AS t
    ON c.parent_object_id = t.[object_id]
    INNER JOIN sys.schemas AS s 
    ON t.[schema_id] = s.[schema_id]
    WHERE c.[type] IN ('D','C','F','PK','UQ')
    and t.[name] NOT IN ('__RefactorLog', 'sysdiagrams')
    and c.name > @constraintName

    -- Then select the Table and Schema associated to the current constraint
    SELECT 
        @tableName = t.name,
        @schemaName = s.name
    FROM sys.objects AS c
    INNER JOIN sys.tables AS t
    ON c.parent_object_id = t.[object_id]
    INNER JOIN sys.schemas AS s 
    ON t.[schema_id] = s.[schema_id]
    WHERE c.name = @constraintName

    -- Then Print to the output and drop the constraint
    Print 'Dropping constraint ' + @constraintName + '...'
    Exec('ALTER TABLE [' + @schemaName + N'].[' + @tableName + N'] DROP CONSTRAINT [' + @constraintName + ']')
End

Set NoCount OFF
    • 7

相关问题