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!

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: