Compare Page Revisions
« Older Revision - Back to Page History - Newer Revision »
declare @DoubleBar varchar(100) = '/*===============================================================================================*/' ,@SinglBar varchar(100) = '/*-----------------------------------------------------------------------------------------------*/' /*===============================================================================================*/ /* Disable Foreign Keys */ /*===============================================================================================*/ select distinct GroupNum = 10 ,SqlCode = @DoubleBar union select distinct GroupNum = 11 ,SqlCode = '/* Disable Foreign Keys */' union select distinct GroupNum = 12 ,SqlCode = @DoubleBar union select distinct GroupNum = 13 ,SqlCode = 'ALTER TABLE [' + onSchema.name + '].[' + onTable.name + '] NOCHECK CONSTRAINT [' + foreignKey.name + ']' from sysforeignkeys fk inner join sysobjects foreignKey on foreignKey.id = fk.constid inner join sys.objects onTable on fk.fkeyid = onTable.object_id inner join sys.schemas onSchema on onSchema.schema_id = onTable.schema_id inner join sysobjects againstTable on fk.rkeyid = againstTable.id where 1=1 AND againstTable.TYPE = 'U' AND onTable.TYPE = 'U' and ObjectProperty(fk.constid,'CnstIsDisabled') = 0 /*===============================================================================================*/ /* Delete Data */ /*===============================================================================================*/ union select distinct GroupNum = 20 ,SqlCode = @DoubleBar union select distinct GroupNum = 21 ,SqlCode = '/* Delete Data */' union select distinct GroupNum = 22 ,SqlCode = @DoubleBar union select distinct GroupNum = 23 ,SqlCode = 'delete from [' + s.name + '].[' + o.name + ']' FROM sys.objects o inner join sys.schemas s on o.schema_id = s.schema_id where 1=1 and o.type = 'U' and o.name not in ('__MigrationHistory', '__EFMigrationsHistory') /*===============================================================================================*/ /* Re-Seed IDENTITY Fields */ /*===============================================================================================*/ union select distinct GroupNum = 30 ,SqlCode = @DoubleBar union select distinct GroupNum = 31 ,SqlCode = '/* Re-seed IDENTITY Fields */' union select distinct GroupNum = 32 ,SqlCode = @DoubleBar union select distinct GroupNum = 33 ,SqlCode = 'DBCC CHECKIDENT (''[' + s.name + '].[' + o.name + ']'', RESEED, 1);' from sys.objects o inner join sys.columns c on o.object_id = c.object_id inner join sys.schemas s on o.schema_id = s.schema_id WHERE 1=1 and o.name not in ('__MigrationHistory', '__EFMigrationsHistory') and o.type = 'U' and c.is_identity = 1 /*===============================================================================================*/ /* Re-enable Foreign Keys */ /*===============================================================================================*/ union select distinct GroupNum = 40 ,SqlCode = @DoubleBar union select distinct GroupNum = 41 ,SqlCode = '/* Re-enabled Foreign Keys */' union select distinct GroupNum = 42 ,SqlCode = @DoubleBar union select distinct GroupNum = 43 ,SqlCode = 'ALTER TABLE [' + onSchema.name + '].[' + onTable.name + '] CHECK CONSTRAINT [' + foreignKey.name + ']' from sysforeignkeys fk inner join sysobjects foreignKey on foreignKey.id = fk.constid inner join sys.objects onTable on fk.fkeyid = onTable.object_id inner join sys.schemas onSchema on onSchema.schema_id = onTable.schema_id inner join sysobjects againstTable on fk.rkeyid = againstTable.id where 1=1 AND againstTable.TYPE = 'U' AND onTable.TYPE = 'U' and ObjectProperty(fk.constid,'CnstIsDisabled') = 0 order by GroupNum
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.