SQL Server – How to Drop All Constraints from All Tables

constraintddlsql server

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?

Best Answer

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.

Related Question