Início / dba / Perguntas / 90033
Accepted
Aaron Bertrand
Asked: 2015-01-24 10:08:51 +0800 CST

Como descarto todas as restrições de todas as tabelas?

  • 772

Quero eliminar todas as restrições padrão, verificar restrições, restrições exclusivas, chaves primárias e chaves estrangeiras de todas as tabelas em um banco de dados SQL Server. Eu sei como obter todos os nomes de restrição de sys.objects, mas como preencher a ALTER TABLEparte?

sql-server ddl

2 respostas

  1. Best Answer

    Você pode derivar essas informações facilmente juntando -se sys.tables.object_id = sys.objects.parent_object_ida esses tipos de objeto.

    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;

    PRINTestá lá apenas para observar, não para copiar e colar a saída (se você quiser executá-lo, é para isso que serve o comentário EXEC) - se você tiver muitas restrições, ele pode não mostrar o script inteiro porque PRINTé limitado a 4.000 caracteres (8kb). Nesses casos, se você precisar validar o script inteiro, veja esta dica para outras formas de validar o script antes de executá-lo. Por exemplo:

    SELECT CONVERT(xml, @sql);

    Quando estiver satisfeito com a saída, descomente o arquivo EXEC.

    • 45

  2. Comecei com a resposta aceita e modifiquei a estrutura para usar um loop while em vez de construir a instrução sql completa em sql dinâmico. Eu gosto mais disso por vários motivos.

    A consulta não é armazenada na variável @sql grande. Essa implementação permite um Print para cada restrição que é descartada para fins de registro na saída. A execução parecia um pouco mais rápida no meu teste de unidade.

    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

relate perguntas