IN2BI, Microsoft Business Intelligence
data, stories & insights
rss | email | twitter

Truncate all Tables

There are many reasons for me to blog. One of the less vain is to recover and reuse code I wrote earlier. In my current project we have a need to test, test and retest the initial load of the data warehouse. TruncateAllTables

For this purpose I wrote this T-SQL script that:

  • Drops all foreign key constraints;
  • Truncates all the tables;
  • Recreates all foreign key constraints. I dropped earlier.

» Similar Posts

  1. Change Data Capture (CDC) and BIML
  2. Building a data warehouse while the source systems are still in development
  3. SQL Server, SSIS, SSAS and SSRS on ONE Server

» Trackbacks & Pingbacks

    No trackbacks yet.

» Comments

  1. Roland Bouman avatar

    Hi Marco! Nice write-up. I'm not a SQL server expert but I had some doubt about this method. In particular, I'm afraid that, for some reason, the client might lose it (bug in client, accidental user shutdown, network split etc. etc.) right after the drop constraints but before the recreate constraints statement. In which case you'd have to somehow restore the constraint definitions from backup or modeling tool. Probably not a huge problem but good for some unplanned downtime. So I was wondering about this alternative instead:...

    EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

    EXEC sp_msforeachtable "TRUNCATE TABLE ?"

    EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

    This way, at least the constraint DDL is preserved at all times.

    Roland Bouman — July 29, 2014 11:41 PM
  2. Marco Schreuder avatar

    Thank you Roland,

    Good remark/suggestion.

    In fact I like your code even better.

    (Learning something every day)

    Marco Schreuder — July 29, 2014 11:51 PM

Comments are closed