Ever had the need to quickly execute a T-SQL script and generate SQL statements in SQL Server 2008 to drop a primary key, foreign keys, check constraints and/or defaults from a table ... Here is one script that would help you do this. You can pass few parameters and the script would generate all the required data with the alter statements. Create the SP in your database and you can run the SP using the following signature:
-- Generate SQL Statement to DROP the Primary Key of a table
EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'PK', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'
-- Generate SQL Statement to DROP the Foreign Keys of a table
EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'FK', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'
-- Generate SQL Statement to DROP the Check Constraints of a table
EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'CK', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'
-- Generate SQL Statement to DROP the Default Constraints of a table
EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'DF', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'
