Description of script is self explanatory and has been tested many times in production
DECLARE @intMthsToKeep int DECLARE @time_cutoff datetime –declare in months how much data you want to keep SET @intMthsToKeep = 1 SET @time_cutoff = DATEADD( Month, -@intMthsToKeep, getdate()) — check for timestamp details before running the actual delete statement print @time_cutoff –deleting in batches of 500 WHILE 1=1 BEGIN DELETE TOP (500) FROM [schemaname].[tablename1] WHERE [Sch_Datetime] < @time_cutoff DELETE TOP (500) FROM [schemaname].[tablename2] WHERE [Sch_Datetime] < @time_cutoff —Check for exit condition IF @@rowcount <500 break END
T-SQL script to purge all the tables including foreign key references. The script has been made smart enough to use TRUNCATE wherever there is no foreign key reference and to use DELETE wherever there is foreign key reference.
2019-12-10
2,517 reads
T-SQL script to purge all the tables including foreign key references. The script has been made smart enough to use TRUNCATE wherever there is no foreign key reference and to use DELETE wherever there is foreign key reference.
2019-12-10
1 reads
