SQL Server – Drop All Table Constraints

Drop All Table Constraints

You want to redefine all the constraints set on a Table. To do that you need to first drop all the existing constraints set on the table. Following is the simple script to do so the same:

— To drop all Constraints on a table Employee

DECLARE @database NVARCHAR(50)

DECLARE @table NVARCHAR(50)

DECLARE @sql NVARCHAR(255)

SET @database = ‘DB_MyDatabase’

SET @table = ‘Employee’

WHILE EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE constraint_catalog=@database AND table_name=@table)

BEGIN

SELECT @sql = ‘ALTER TABLE ‘ + @table + ‘ DROP CONSTRAINT ‘ + CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE constraint_catalog=@database AND table_name=@table

EXEC sp_executesql @sql

END

The above script does not drop DEFAULT constraint set on the Table.

See also:

SQL Server – List all Constraints of Database or Table

SQL Server – List all Foreign Key Constraints of Database or Table

Good Luck!