Skip to content

Instantly share code, notes, and snippets.

@in2bi in2bi/TruncateAllTables
Last active Aug 29, 2015

Embed
What would you like to do?
TSQL Script to truncate all tables in a database
/*
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.