Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Generate DDL script to drop table constraints

By VPSD Gupta,

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'

Total article views: 2522 | Views in the last 30 days: 6
 
Related Articles
SCRIPT

Generate DDL script to create table and all constraints

Generate all DDL statements for a given table (Create Table, PK, FK, Check Constraints, Defaults)

FORUM

Check Constraint with Case Statement

Check Constraint with Case Statement

ARTICLE

Beware to the System Generated Constraint Name

New columnist Gregory Larsen used to not care what his objects were called until recently when he be...

ARTICLE

Generating Insert Statements

This article from new author Oleg Netchaev describes the cursor-less script used to generate insert ...

SCRIPT

MERGE Statement Generator

Generates the merge statement for a given table.

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones