Drop all the constraints on a table

Following sql snippet illustrates an example of dropping all the constraints on a table. This stored procedure takes table name as parameter and dynamically creates a drop script for all the constraints of given table and executes it. Earliar today I was searching scripts concerning the same but I mostly found scripts related to information schema of the database. As a small developer I thought it was risky to put a hand on that. I was able to create this simple script which just involves a cursor on sysobjects table.

CREATE PROCEDURE DeleteConstraints
@tableName VARCHAR(100)
AS
BEGIN

DECLARE @sql VARCHAR(max)
DECLARE @conName VARCHAR(100)
DECLARE @con CURSOR

SET @con = CURSOR FOR SELECT name FROM sysobjects WHERE parent_obj=(SELECT id FROM sysobjects WHERE name=@tableName) AND type IN ('C','D','F','K')

OPEN @con
FETCH NEXT
FROM @con INTO @conName
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = 'IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[' + @conName + ']'') AND type IN (''C'',''D'',''F'',''K'')
BEGIN
	ALTER TABLE [dbo].[' + @tableName  + '] DROP CONSTRAINT [' + @conName + ']
END'
	EXEC(@sql)

FETCH NEXT
FROM @con INTO @conName
END
CLOSE @con
DEALLOCATE @con

END
GO
Advertisements

About Utkarsh Puranik

Software Engineer by Profession, Gamer by Nature, Techy by Attitude and a Good Person at Heart
This entry was posted in Technology and tagged , , , , . Bookmark the permalink.

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