
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”