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”