Navigation

How to Delete Contraints in SQL Server

delete constraints in sql server, disable all constraints in sql server database, mssql disable constraints

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.


Delete Constraints in SQL Server

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 =

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

Share

Anjan Kant

Outstanding journey in Microsoft Technologies (ASP.Net, C#, SQL Programming, WPF, Silverlight, WCF etc.), client side technologies AngularJS, KnockoutJS, Javascript, Ajax Calls, Json and Hybrid apps etc. I love to devote free time in writing, blogging, social networking and adventurous life

Post A Comment:

0 comments: