USP_DropTableConstraints_2005

Drops all constraints of the specified type from a table.
Takes database name, schema name, table name and constraint type as parameters.
Valid constraint types are ; 'PRIMARY KEY', 'FOREIGN KEY', 'CHECK'

Usage :
EXEC USP_DropTableConstraints_2005 'AdventureWorksTarget', 'Person', 'Contact', 'CHECK'

CREATE PROCEDURE USP_DropTableConstraints_2005
 @dbname VARCHAR(128),
 @schemaname VARCHAR(128),
 @tablename VARCHAR(128),
@constrainttype VARCHAR(128)
       
AS
-- USP_DropTableConstraints_2005 by sql solace
DECLARE  @sqlstring NVARCHAR(500)

SET @constrainttype = UPPER(LTRIM(RTRIM(@constrainttype)))

WHILE EXISTS (SELECT *
            FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
            WHERE  CONSTRAINT_CATALOG = @dbname
            AND    TABLE_SCHEMA = @schemaname
            AND    TABLE_NAME = @tablename
     AND    CONSTRAINT_TYPE = @constrainttype)
BEGIN
  SELECT @sqlstring = 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] DROP CONSTRAINT ' + CONSTRAINT_NAME
  FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  WHERE  CONSTRAINT_CATALOG = @dbname
  AND    TABLE_SCHEMA = @schemaname
  AND    TABLE_NAME = @tablename
  AND    CONSTRAINT_TYPE = @constrainttype
  PRINT @sqlstring
  EXECUTE sp_executesql  @sqlstring
END
GO