Truncate tables with foreign keys

neeleshsharmadba, 2016-12-01 (first published: 2016-11-28)

There are two places you need to edit.

1) There is a table variable in the begining that needs to be replaced with the tables that need to be truncated. You can replace the row constructor with a select query if required.

2) Change the ValidateOnly variable to 0 if executing directly. It is not recomended unless its tested already and no one care about those tables.

Idealy this script should be used to quickly prepare the truncate statements.

Please post a comment if there are any issues/ suggestions

DECLARE @TablesToTruncate TABLE (SchemaName Sysname,TableName Sysname) SET NOCOUNT ON -- Add the list of tables that needs to be truncated. --(SchemaNme, Tablename), DECLARE @ValidateOnly INT =1 -- DO NOT CHANGE IT TO 0 UNLESS ITS READY FOR TRUNCATE ! INSERT INTO @TablesToTruncate Values ('dbo','Table1') ,('dbo','Table2') -- Generate Scripts and/or execute DECLARE @FKDrop VARCHAR(Max)='',@FKCreate varchar(max)='',@TruncateSQL varchar(max)='' BEGIN TRY BEGIN TRAN SELECT @FKDrop=@FKDrop+'IF EXISTS (SELECT 1 from Sys.foreign_keys where Name='''+FK.name+''') ALTER TABLE ['+schema_name(FK.schema_id)+'].['+OBJECT_NAME(FK.parent_object_id)+'] DROP CONSTRAINT '+FK.name +';'+char(13) FROM sys.foreign_keys FK Join Sys.tables ST on ST.object_id=FK.parent_object_id JOIN @TablesToTruncate T on object_name(FK.referenced_object_id)=T.TableName AND Schema_name(FK.schema_id)=T.SchemaName SELECT @FKCreate=@FKCreate+'IF NOT EXISTS (SELECT 1 from Sys.foreign_keys where Name='''+FK.name+''') ALTER TABLE ['+schema_name(FK.schema_id)+'].['+OBJECT_NAME(FK.parent_object_id)+'] ADD CONSTRAINT '+FK.name +' FOREIGN KEY (' + STUFF((Select ','+C.name from Sys.columns C Join sys.foreign_key_columns FKC on Fkc.parent_column_id=C.column_id and c.object_id=FKC.parent_object_id WHERE FKC.constraint_object_id=FK.object_id FOR XML PATH('')),1,1,'') + ' ) REFERENCES ['+ +schema_name(ST.schema_id)+'].['+OBJECT_NAME(FK.referenced_object_id)+'] (' + STUFF((Select ','+C.name from Sys.columns C Join sys.foreign_key_columns FKC on Fkc.referenced_column_id=C.column_id and c.object_id=FKC.referenced_object_id WHERE FKC.constraint_object_id=FK.object_id FOR XML PATH('')),1,1,'') + ') ;'+char(13) FROM sys.foreign_keys FK Join Sys.tables ST on ST.object_id=FK.parent_object_id JOIN @TablesToTruncate T on object_name(FK.referenced_object_id)=T.TableName AND Schema_name(ST.schema_id)=T.SchemaName Select @TruncateSQL=@TruncateSQL +'TRUNCATE TABLE ['+schema_name(ST.schema_id)+'].['+OBJECT_NAME(ST.Object_id)+'];'+char(13) FROM Sys.tables ST JOIN @TablesToTruncate T on object_name(ST.object_id)=T.TableName AND Schema_name(ST.schema_id)=T.SchemaName PRINT '--* DROP FKS Scripts' PRINT @FKDrop IF @ValidateOnly=0 EXEC( @FKDrop) PRINT '--* Truncate tables Scripts' PRINT @TruncateSQL IF @ValidateOnly=0 EXEC( @TruncateSQL) IF @@TRANCOUNT>0 COMMIT TRAN IF @ValidateOnly=0 PRINT ' Succesfully Completed Truncate !' END TRY BEGIN CATCH PRINT ERROR_MESSAGE() IF @@TRANCOUNT>0 BEGIN ROLLBACK TRAN PRINT ' Rollback Completed.' END END CATCH PRINT '--* Recreate FKs Scripts' PRINT @FKCreate IF @ValidateOnly=0 EXEC( @FKCreate)