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

评论

出于好奇,这种请求的背景是什么?我想知道如何解决功能依赖性(即索引视图,FK上的级联事件以及具有IGNORE_DUP_KEY = ON的UQ)。

@srutzky有人问到Stack Overflow,但我决定在这里创建一个更干净,规范的版本。无论如何,这是一个常见的请求,通常是清理数据库(重新开始,清理错误地放入主数据库的对象等)较大任务的一部分。我看不到这些功能依赖项会因删除约束而受到影响-实际上,我怀疑在大多数情况下,较大的图片也会被截断或删除表。首先删除约束将允许这样做。

#1 楼

您可以通过为这些对象类型加入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的注释。

评论


您可能还需要确保将外键约束放在主键之前; ORDER BY(当c。[type] IN(“ PK”,“ UQ”)然后1到0结束时的情况)

–丹尼尔·赫特马赫(Daniel Hutmacher)
15年1月25日在18:16

@Daniel的好处是,在SQL Server引入新的约束类型之前,ORDER BY类型可能就足够了。

–亚伦·伯特兰(Aaron Bertrand)
15年1月25日在18:43

您可以使用select语句查看而不是打印然后复制并粘贴-这似乎对我有用:SELECT 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 t ON c.parent_object_id = t。[object_id] INNER JOIN sys.schemas AS s ON t。[schema_id] = s。[schema_id] W.C. [type] IN( 'D','C','F','PK','UQ')ORDER BY c。[type];

–TheNerdyNerd
20-10-26在20:04

@TheNerdyNerd PRINT仅用于验证第一个8K,也可以将其更改为SELECT @sql;。但随后您必须向用户提供指示,以更改默认的输出限制,并从网格更改为文本,以免丢失CR / LF。没有人说过使用打印然后复制和粘贴...那里有一个EXEC,出于安全考虑已注释掉,这样您就不必复制和粘贴任何内容(如果直接使用SELECT则仍然必须这样做)。

–亚伦·伯特兰(Aaron Bertrand)
20-10-27在12:24



@TheNerdyNerd同样不确定我是否理解您关于下一行空间的观点。 T-SQL并不真正关心空格,您可以随意设置其格式。我针对此处的展示进行了优化,而不是最终的输出。

–亚伦·伯特兰(Aaron Bertrand)
20-10-28在13:42

#2 楼

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

查询未存储在大型@sql变量中。此实现允许为输出中记录的每个约束删除Print。在我的单元测试中,执行似乎要快一些。

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