Problem
Solution
T1 | T2 |
T3 | ItemTable |
A solution that can TRUNCATE all tables
- Create a table variable to store the constraint drop and creation scripts for the database
- Load the data for all tables in the database
- Execute a cursor to drop all constraints
- Truncate all tables
- Recreate all the constraints
/* TRUNCATE ALL TABLES IN A DATABASE */ DECLARE @dropAndCreateConstraintsTable TABLE ( DropStmt VARCHAR(MAX) ,CreateStmt VARCHAR(MAX) ) /* Gather information to drop and then recreate the current foreign key constraints */ INSERT @dropAndCreateConstraintsTable SELECT DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema + '].[' + ForeignKeys.ForeignTableName + '] DROP CONSTRAINT [' + ForeignKeys.ForeignKeyName + ']; ' ,CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema + '].[' + ForeignKeys.ForeignTableName + '] WITH CHECK ADD CONSTRAINT [' + ForeignKeys.ForeignKeyName + '] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn + ']) REFERENCES [' + SCHEMA_NAME(sys.objects.schema_id) + '].[' + sys.objects.[name] + ']([' + sys.columns.[name] + ']); ' FROM sys.objects INNER JOIN sys.columns ON ( sys.columns.[object_id] = sys.objects.[object_id] ) INNER JOIN ( SELECT sys.foreign_keys.[name] AS ForeignKeyName ,SCHEMA_NAME(sys.objects.schema_id) AS ForeignTableSchema ,sys.objects.[name] AS ForeignTableName ,sys.columns.[name] AS ForeignTableColumn ,sys.foreign_keys.referenced_object_id AS referenced_object_id ,sys.foreign_key_columns.referenced_column_id AS referenced_column_id FROM sys.foreign_keys INNER JOIN sys.foreign_key_columns ON ( sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.[object_id] ) INNER JOIN sys.objects ON ( sys.objects.[object_id] = sys.foreign_keys.parent_object_id ) INNER JOIN sys.columns ON ( sys.columns.[object_id] = sys.objects.[object_id] ) AND ( sys.columns.column_id = sys.foreign_key_columns.parent_column_id ) ) ForeignKeys ON ( ForeignKeys.referenced_object_id = sys.objects.[object_id] ) AND ( ForeignKeys.referenced_column_id = sys.columns.column_id ) WHERE ( sys.objects.[type] = 'U' ) AND ( sys.objects.[name] NOT IN ( 'sysdiagrams' ) ) /* SELECT * FROM @dropAndCreateConstraintsTable AS DACCT --Test statement*/ DECLARE @DropStatement NVARCHAR(MAX) DECLARE @RecreateStatement NVARCHAR(MAX) /* Drop Constraints */ DECLARE Cur1 CURSOR READ_ONLY FOR SELECT DropStmt FROM @dropAndCreateConstraintsTable OPEN Cur1 FETCH NEXT FROM Cur1 INTO @DropStatement WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Executing ' + @DropStatement EXECUTE sp_executesql @DropStatement FETCH NEXT FROM Cur1 INTO @DropStatement END CLOSE Cur1 DEALLOCATE Cur1 /* Truncate all tables in the database in the dbo schema */ DECLARE @DeleteTableStatement NVARCHAR(MAX) DECLARE Cur2 CURSOR READ_ONLY FOR SELECT 'TRUNCATE TABLE [dbo].[' + TABLE_NAME + ']' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE' /* Change your schema appropriately if you don't want to use dbo */ OPEN Cur2 FETCH NEXT FROM Cur2 INTO @DeleteTableStatement WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Executing ' + @DeleteTableStatement EXECUTE sp_executesql @DeleteTableStatement FETCH NEXT FROM Cur2 INTO @DeleteTableStatement END CLOSE Cur2 DEALLOCATE Cur2 /* Recreate foreign key constraints */ DECLARE Cur3 CURSOR READ_ONLY FOR SELECT CreateStmt FROM @dropAndCreateConstraintsTable OPEN Cur3 FETCH NEXT FROM Cur3 INTO @RecreateStatement WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Executing ' + @RecreateStatement EXECUTE sp_executesql @RecreateStatement FETCH NEXT FROM Cur3 INTO @RecreateStatement END CLOSE Cur3 DEALLOCATE Cur3 GO