Click here to Skip to main content
15,867,771 members
Articles / Productivity Apps and Services / Sharepoint

Clearing Down a Database Full of Constraints in SQL Server

Rate me:
Please Sign up or sign in to vote.
4.27/5 (3 votes)
4 Jul 2016CPOL1 min read 6.4K   3   1
How to clear down a database full of constraints in SQL Server

Clearing Down a Database Full of Constraints in SQL Server

Have you ever been in a scenario where you have to clear down some data within a database that is chock full of constraints but don't want to wipe out your precious relationships, indices and all that other jazz?

I found myself in a similar situation earlier this week and needed a clear-down script that would wipe out all of the data within an entire database without being bothered by any existing constraints and here it is:

SQL
USE @YourTable;  
EXEC sp_MSForEachTable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"  
EXEC sp_MSForEachTable "DELETE FROM ?"  
EXEC sp_MSForEachTable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"  
GO 

What Is This Doing?

The script itself takes advantage of an undocumented stored procedure within SQL Server called sp_MSForEachTable that will actually iterate through all of the tables within a given database.

Now that we know we are going to be looping through each of the tables within the specified database, let's see what is going to happen to each of the tables:

  • ALTER TABLE ? NOCHECK CONSTRAINT ALL - This will disable any constraint checking that is present on the table (so operations like deleting a primary key or a related object won't trigger any errors).
  • DELETE FROM ? - This will delete every record within the table.
  • ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL - This re-enables the constraint checking, bringing your table back to its original state, sans data.

Note: It is very important that you properly scope this query to the table that you are targeting to avoid any crazy data loss.

While I don't think that you could just leave that out and execute on master, I wouldn't want to even risk testing that out (although feel free to try it out and let me know if it nukes everything).

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United States United States
An experienced Software Developer and Graphic Designer with an extensive knowledge of object-oriented programming, software architecture, design methodologies and database design principles. Specializing in Microsoft Technologies and focused on leveraging a strong technical background and a creative skill-set to create meaningful and successful applications.

Well versed in all aspects of the software development life-cycle and passionate about embracing emerging development technologies and standards, building intuitive interfaces and providing clean, maintainable solutions for even the most complex of problems.

Comments and Discussions

 
QuestionIf your database is huge ... Pin
Phil Parkin6-Jul-16 6:58
Phil Parkin6-Jul-16 6:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.