ray.so

Drop All Constraints From All Tables Of A Database ​

This is a SQL script that generates a dynamic SQL statement to drop all constraints of certain types (D: Default constraints, C: Check constraints, F: Foreign Key constraints, PK: Primary Key constraints, UQ: Unique constraints) from all tables in the database.

The script first declares a variable called @sql of type NVARCHAR(MAX) and initializes it to an empty string. Then it selects all the constraints of the specified types from the system tables and builds a dynamic SQL statement by concatenating the ALTER TABLE statement with the names of the schema, table, and constraint, and appends it to @sql.

Finally, the script prints the generated SQL statement to the console using the PRINT statement. If you want to execute the generated SQL statement, you can uncomment the last line of the script, which uses the sp_executesql stored procedure to execute the dynamic SQL statement.

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;