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