[SQL SERVER] limpar database - truncate

Segue uma rotina para limpar a data base usando truncate table.
obs: Lembrando que truncate zera todos os identitys

IF EXISTS (Select [name] from sys.tables where [name] = 'TAB_FK_TRUNCATE' and type = 'U')
truncate table TAB_FK_TRUNCATE
go

IF NOT EXISTS (Select [name] from sys.tables where [name] = 'TAB_FK_TRUNCATE' and type = 'U')
Create table NomeDaDatabase.dbo.TAB_FK_TRUNCATE (
ID int identity (1,1),
NomeDaConstraint varchar (255),
TabelaPai varchar(255),
ColunaPaiPK varchar(255),
TabelaFilha varchar(255),
ColunaFilhaFK varchar(255),
FKOrder int
)
go

insert into NomeDaDatabase.dbo.TAB_FK_TRUNCATE(NomeDaConstraint,TabelaPai,ColunaPaiPK,TabelaFilha,ColunaFilhaFK,FKOrder)
SELECT object_name(constid) as NomeDaConstraint,object_name(rkeyid) TabelaPai
,sc2.name ColunaPaiPK
,object_name(fkeyid) TabelaFilha
,sc1.name ColunaFilhaFK
,cast (sf.keyno as int) FKOrder
FROM sysforeignkeys sf
INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid
INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid
ORDER BY rkeyid,fkeyid,keyno


declare @NomeDaConstraint varchar (max)
declare @TabelaFilha varchar (max)
declare @TabelaPai varchar (max)
declare @ColunaFilhaFK varchar (max)
declare @ColunaPaiPK varchar (max)
declare @FKOrder smallint
declare @sqlcmd varchar (max)


declare drop_constraints cursor
fast_forward
for
SELECT object_name(constid) as NomeDaConstraint,object_name(rkeyid) TabelaPai
,sc2.name ColunaPaiPK
,object_name(fkeyid) TabelaFilha
,sc1.name ColunaFilhaFK
,cast (sf.keyno as int) FKOrder
FROM sysforeignkeys sf
INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid
INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid
ORDER BY rkeyid,fkeyid,keyno

open drop_constraints
fetch next from drop_constraints
into
@NomeDaConstraint
,@TabelaPai
,@ColunaPaiPK
,@TabelaFilha
,@ColunaFilhaFK
,@FKOrder
while @@Fetch_status = 0
begin


select @sqlcmd = 'alter table '+@TabelaFilha+' drop constraint '+@NomeDaConstraint--+' foreign key '+'('+@ColunaFilhaFK+')'+' references '+@TabelaPai+' ('+@ColunaPaiPK+')'+' on delete no action on update no action'
If EXISTs (select object_name(constid) from sysforeignkeys where object_name(constid) = @NomeDaConstraint)
exec (@sqlcmd)
fetch next from drop_constraints
into
@NomeDaConstraint
,@TabelaPai
,@ColunaPaiPK
,@TabelaFilha
,@ColunaFilhaFK
,@FKOrder
end
close drop_constraints
deallocate drop_constraints

go

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

go

EXEC sp_MSForEachTable 'truncate TABLE ? '
print 'All tables truncated'
go

obs: Não utilizem em sistemas feitos em maker, pois essa rotina limpa todas as tabelas FR também

Fonte: databasejournal