32

I want to drop all default constraints, check constraints, unique constraints, primary keys and foreign keys from all tables in a SQL Server database. I know how to get all the constraint names from sys.objects, but how do I populate the ALTER TABLE part?

  • Just out of curiosity, what is the context of such a request? Am wondering how functional dependencies are addressed (i.e. indexed views, cascade events on FKs, and UQs that had IGNORE_DUP_KEY = ON). – Solomon Rutzky Jan 25 '15 at 20:14
  • 3
    @srutzky It was asked on Stack Overflow but I decided to create a cleaner, canonical version here. Anyway it is a common request, often part of a larger task of cleaning out a database (starting over, cleaning up objects that were mistakenly put into master, etc). I don't see these functional dependencies being impacted by dropping the constraints - in fact I suspect that in most cases the larger picture is truncating or dropping the tables, too. Dropping the constraints first allows that. – Aaron Bertrand Jan 25 '15 at 20:23
40

You can derive this information easily by joining sys.tables.object_id = sys.objects.parent_object_id for those object types.

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 is just there for eyeballing, not for copying and pasting the output (if you want to run it, that's what the commented-out EXEC is for) - if you have a lot of constraints, it may not show the entire script because PRINT is limited to 4,000 characters (8kb). In those cases, if you need to validate the entire script, see this tip for other ways to validate the script before running. For example:

SELECT CONVERT(xml, @sql);

Once you are happy with the output, uncomment the EXEC.

  • 4
    You might also want to make sure to drop foreign key constraints before primary keys; ORDER BY (CASE WHEN c.[type] IN ('PK', 'UQ') THEN 1 ELSE 0 END) – Daniel Hutmacher Jan 25 '15 at 18:16
  • 1
    @Daniel good point, ORDER BY type is probably sufficient until SQL Server introduces new constraint types. – Aaron Bertrand Jan 25 '15 at 18:43
  • You can just use a select statement to view rather than print then copy and paste - this seemed to work for me: 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 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]; – TheNerdyNerd Oct 26 '20 at 20:04
  • @TheNerdyNerd PRINT is just for validation of the first 8K, you could also just change it to SELECT @sql; but then you have to give instructions to users to change the default output limit and also change from grid to text so you don't lose CR/LF. Nobody said to use print then copy and paste... there is an EXEC there, commented out for safety, so that you don't have to copy and paste anything (which you still have to do if you just use SELECT directly). – Aaron Bertrand Oct 27 '20 at 12:24
  • 1
    @TheNerdyNerd Also not sure I understand your point about the space in the next line. T-SQL doesn't really care about whitespace and you are free to format it however you like. I optimized for presentation here, not eventual output. – Aaron Bertrand Oct 28 '20 at 13:42
6

I started with the accepted answer and modified the structure to use a while loop rather than to build the full sql statement in dynamic sql. I like this better for several reasons.

The query is not stored in the large @sql variable. This implementation allows for a Print for each constraint that is dropped for logging purposes in the output. Execution seemed a little faster in my unit testing.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.