Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

Drop all the constraints on a table

Posted by Viral Sarvaiya on December 19, 2009

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

One Response to “Drop all the constraints on a table”

  1. LT Arnold said

    what a great site and informative posts, I will add a backlink and bookmark your site. Keep up the good work!

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

 
%d bloggers like this: