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!
2 thoughts on “How to Truncate/Delete all Tables in a SQL Server Database?”
-scripting the foreign keys to drop
-truncate the tables
-and recreating the f-keys
Yup! You can do that way….It’s perfectly Okay! As I mentioned Initially even I thought of doing the same…But I had pain couple of times while adding back FK’s from my scripts in the past..so thought of this. YMMV!