How to Delete Contraints in SQL Server

Introduction

Suppose we are working with Entity Framework or some very complex database and moreover we don’t have matured database then very tedious work to carry out changes with our back end database in MS SQL Server, so I am proposing this great utility to handle out this problem.

Today, I am writing about how to delete constraints in MS SQL Server through single query command. Often of us, spend a lot of time to locate constraints and try to delete constraints one by one, it takes a long time. We wasted our precious development time such type of tedious work and also create some confusions.

Declaring variables required to run SQL query

DECLARE @sql nvarchar(255)

DECLARE @For_TABLE_NAME varchar(50);

DECLARE @CONSTRAINT_NAME varchar(50);

Declare Cursor

DECLARE For_TABLE_NAME CURSOR FOR 

Run Cursor to fetch records from INFORMATION_SCHEMA

SELECT TABLE_NAME,CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

OPEN For_TABLE_NAME   ORDER BY TABLE_NAME

FETCH NEXT FROM For_TABLE_NAME  INTO @For_TABLE_NAME, @CONSTRAINT_NAME    

Run cursor up to end of constraints in our database

WHILE @@FETCH_STATUS = 0 

Close and Deallocate Cursor

Here we close cursor and then deallocate from the memory to optimize our database performance.

CLOSE For_TABLE_NAME  

DEALLOCATE For_TABLE_NAME

Complete MS SQL Query View:

DECLARE @For_TABLE_NAME varchar(50);

DECLARE @sql varchar (700);

DECLARE @CONSTRAINT_NAME varchar(50);

DECLARE For_TABLE_NAME CURSOR FOR 

SELECT TABLE_NAME,CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS order by TABLE_NAME

OPEN For_TABLE_NAME  

FETCH NEXT FROM For_TABLE_NAME  INTO @For_TABLE_NAME, @CONSTRAINT_NAME   

WHILE @@FETCH_STATUS = 0  

BEGIN  

          --

               PRINT 'fetching next:' + @CONSTRAINT_NAME;

                     PRINT 'ready to drop constraint' + @CONSTRAINT_NAME;

                     SELECT    @sql = 'ALTER TABLE ' + @For_TABLE_NAME + ' DROP CONSTRAINT ' + @CONSTRAINT_NAME

                     from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS

                     exec    sp_executesql @sql

                     PRINT 'deleted:' + @CONSTRAINT_NAME;

                     FETCH NEXT FROM For_TABLE_NAME INTO @For_TABLE_NAME, @CONSTRAINT_NAME

                     PRINT 'fetching next:' + @CONSTRAINT_NAME;

END  

CLOSE For_TABLE_NAME  

DEALLOCATE For_TABLE_NAME

Conclusion

So with this query we are fully able to delete all constraints in a single query so that we can save our precious time of our development. It will be helpful to truncate/delete MS SQL Server database objects from our database but one thing keep in mind while run this query, you should make assure that you are in right direction when going towards this powerful action. 

Relevant Reading

• Could not open a connection to SQL Server
• How to Schedule Backup in SQL Server
• How to Design Entity Relationship Diagram
• DDL vs DML