Truncate all Tables

TruncateAllTables

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. 

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.
/*
Truncate all tables in a database, by:
– dropping all foreign key constraints
– truncating all the tables
– recreating the dropped foreign key constraints
*/
DECLARE @DropConstraints nvarchar(max)
DECLARE @TruncateTables nvarchar(max)
DECLARE @RecreateConstraints nvarchar(max)
SELECT
— DROP CONSTRAINTS
@DropConstraints = STUFF(
(
SELECT char(10) + ‘ALTER TABLE ‘
+ QUOTENAME(OBJECT_SCHEMA_NAME(f.parent_object_id))
+ ‘.’ + QUOTENAME(OBJECT_NAME(f.parent_object_id))
+ ‘ DROP CONSTRAINT ‘ + QUOTENAME(f.name)
FROM .sys.foreign_keys f
JOIN .sys.foreign_key_columns fc
ON f.OBJECT_ID = fc.constraint_object_id FOR XML PATH(”)
)
,1,1,”)
— TRUNCATE TABLES
, @TruncateTables = STUFF(
(
SELECT char(10) +’TRUNCATE TABLE ‘
+ QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
+ ‘.’ + QUOTENAME(OBJECT_NAME(object_id))
FROM sys.tables FOR XML PATH(”)
)
,1,1,”)
— RECREATE CONSTRAINTS
, @RecreateConstraints = STUFF(
(
SELECT char(10) + ‘ALTER TABLE ‘
+ QUOTENAME(OBJECT_SCHEMA_NAME(f.parent_object_id))
+ ‘.’ + QUOTENAME(OBJECT_NAME(f.parent_object_id))
+ ‘ ADD CONSTRAINT ‘ + QUOTENAME(f.name)
+ ‘ FOREIGN KEY ( ‘+ COL_NAME(fc.parent_object_id, fc.parent_column_id) +’ )’
+ ‘ REFERENCES ‘ + QUOTENAME(OBJECT_SCHEMA_NAME (f.referenced_object_id))
+ ‘.’ + QUOTENAME(OBJECT_NAME (f.referenced_object_id))
+ ‘ ( ‘ + COL_NAME(fc.referenced_object_id, fc.referenced_column_id) +’ )’
FROM .sys.foreign_keys f
JOIN .sys.foreign_key_columns fc
ON f.OBJECT_ID = fc.constraint_object_id FOR XML PATH(”)
)
,1,1,”)
PRINT @DropConstraints
PRINT ‘————————————————–‘
PRINT @TruncateTables
PRINT ‘————————————————–‘
PRINT @RecreateConstraints
EXEC sp_executesql @DropConstraints;
EXEC sp_executesql @TruncateTables;
EXEC sp_executesql @RecreateConstraints

As an alternative based on a comment by Roland Bouman you can also use..

EXEC sp_msforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”
EXEC sp_msforeachtable “TRUNCATE TABLE ?”
EXEC sp_msforeachtable “ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”