How to Truncate/Delete all Tables in a SQL Server Database?

How to Truncate/Delete all Tables in a SQL Server Database in three simple steps????

Howdy! In this post let’s see how to truncate/delete all the tables in a given SQL Server Database and few important considerations while doing so.

Today, I had a request from one of my Application teams to copy a database from Prod to QA and truncate all the tables. It was a very small Database(7.2 GB) but had 250+ tables with manyyyyyyy Foreign Keys. So, I had to deal with Constraints before SQL Server allows me to truncate any table which is being referenced by some other table. Basically I’ve to follow the child-parent hierarchy to do so. I started with scripting out all the Foreign Keys and thought of dropping all of them and do a truncate and then recreating. Well, immediately something came to my mind….”How about Disabling Constraints and do my work and then re-enabling Constraints”

Imp Note: We can’t Truncate tables with just Disabling Constraints. SQL allows only if you drop them physically.

So, what is the Option I’m left with. Well, Delete is my weapon now.

How I did this was..

Step 1:  Disable all the Constrains

Exec sp_msforeachtable “Alter Table ? NOCHECK Constraint all”

Step 2: Delete data from all the Tables

Exec sp_msforeachtable “Delete From ?”

Step 3: Re-enable all the constraints which we disabled in Step 1

Exec sp_msforeachtable “Alter Table ? With Check Check Constraint all”

I Know, I Know….sp_msforeachtable is old school technique and deprecated, but am Okay as long as I Know there’s no harm using it and making my life easy 😀

So, what should we keep in mind if we are considering this approach??

The Very First thing is your Transaction log. How much room do you’ve for your T-log to grow is single most important consideration for this approach. Because Step2 will run as a single Hugee…..transaction. Let me say this in other Words, My Database was in SIMPLE Recovery Mode and I was monitoring the T-Log constantly..It grew upto 15.4 GB for doing this Delete(and the entire Database Size was 7.2 GB). Just Make sure….you are good here before taking this approach for a Medium-Large Database. Once you are done with this, do not forget to bring down your T-Log size accordingly.

Hope this helps!