Procedure para deletar dados de todas as tabelas do bd

Question

Salve!

 

Boa tarde, estou com um pequeno problema.

Ao final de cada ano tenho que zerar meu banco de dados para passar pro proximo ano, antes, quando era só 1 BD, eu ia de tabela em tabela e dava um "delete from tabela", mas isso não compensa pois agora tenho que fazer isso com mais de 10 BD's. Pensei em criar uma procedure que ao ser executada delete todos os dados, mas não estou conseguindo fazer, alguem já fez isso???

 

Se puderem contribuir ficarei muito grato.

 

Obrigado pela atenção.

Answer 1

vc pode fazer assim..

executar o comando..

select ' delete from ' + TABLE_NAME from INFORMATION_SCHEMA.TABLES

e roda o retorno..vai deletar todas as tabelas

 

se for criar procedure.. vc tera que criar um cursor, para percorrer todas as tabelas e dando delete por delete

 

[]s

Answer 2

Cara, faz um loop nas sysobjects de cada banco e pra cada vez que ele passa no loop você concatena o comando Truncate table + nome da tabela ou delete from + nome da tabela e manda fazer um execute desse comando.

 

Conseguiu entender?

 

Caso preciso de uma ajuda pra elaborar isso, é só falar.

 

Abraços,

Answer 3

Boa Tarde,

 

Eu havia postado algo parecido no Technet.

 

Code Block

DECLARE tblTemp CURSOR

FAST_FORWARD

FOR

SELECT NAME FROM SYSOBJECTS WHERE TYPE = 'U'

GO

OPEN tblTemp

DECLARE @tabela sysname, @cmdSQL NVARCHAR(100)

FETCH NEXT FROM tblTemp INTO @tabela

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmdSQL = 'DELETE FROM ' + @tabela

EXEC(@tabela)

FETCH NEXT FROM tblTemp INTO @tabela

END

CLOSE tblTemp

DEALLOCATE tblTemp

GO

 

 

No entanto, esse tipo de script é falho. Em nenhuma das soluções apontadas estamos prevendo a ordem correta das tabelas para evitar problemas com a integridade referencial. Você pode precisar executar mais de uma vez para se certificar de que todos os dados foram excluídos.

 

[ ]s,

 

Gustavo

 

 

 

 

Answer 4

Boa Tarde,

 

Minha solução seria boa se fosse necessário excluir algumas tabelas. Se a idéia for realmente fazer isso em todas, seria melhor utilizar um stored procedure não documentada (sp_MSforeachtable). Tente o comando abaixo:

 

exec sp_MSforeachtable 'DELETE FROM ?'

 

Ou

 

exec sp_MSforeachtable 'TRUNCATE TABLE ?'

 

[ ]s,

 

Gustavo

Answer 5

 

Gustavo,

 

Eu ia sugerir exatamente isso. Essa SP ajuda em muita coisa!!

 

 

Abraço!!

Answer 6

Ricardo vlw, mas se as tabelas tiverem relacionamento ele naum deleta, pq possui um relacionamento.

Tem alguma maneira de modifiar isso??

Answer 7

Oi Alexandre,

 

Pois é. Essas procedures quebram um galho mesmo...

Elas não tem nada demais, pois, acabam executando um cursor, mas é que utilizá-las é algo bem mais direto.

 

Abraço,

 

Answer 8

ai vc teria que usar um cursor pra desabilitar os enforces das tabelas

 

Declare @Tabela varchar(100)
DECLARE DesabilitaConstraint_cursor CURSOR FOR
SELECT  TABLE_NAME
FROM  INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
WHERE CONSTRAINT_NAME LIKE '%FK%'

OPEN  DesabilitaConstraint_cursor

FETCH NEXT FROM DesabilitaConstraint_cursor
INTO @Tabela

WHILE @@FETCH_STATUS = 0
BEGIN
   EXEC ('ALTER TABLE '+@Tabela+' NOCHECK CONSTRAINT ALL')
   FETCH NEXT FROM DesabilitaConstraint_cursor
   INTO @Tabela
END

CLOSE DesabilitaConstraint_cursor
DEALLOCATE DesabilitaConstraint_cursor

 

e depois de rodar o comando.. rodar de novo e substituir o nocheck por check

Answer 9

Olá Tak,

 

Se houver relacionamentos, você terá que executar até não retornar mais um erro. Ex:

 

TBL1 - C1 (PK)

TBL2 - C2 (PK) C1 (FK)

TBL3 - C3 (PK) C2 (FK)

 

Se o DELETE começar pela TBL1 haverá um problema de integridade e alguns registros não serão deletados (os que tem ocorrências em TBL2). O DELETE irá prosseguir para TBL2 e o mesmo problema irá acontecer. Quando rodar em TBL3 nenhum problema ocorre.

 

Ao executar os DELETEs novamente, TBL1 vai dar problema por conta dos registros em TBL2, mas TBL2 não irá apresentar problemas com TBL3, pois, a mesma já foi excluída.

 

A terceira execução irá eliminar apenas os registros de TBL1, pois em TBL2 e TBL3 os registros já foram eliminados. Isso significa que o comando deve ser executado no máximo N vezes sendo N igual ao número de tabelas envolvidas. Essa lógica só não será verdadeira se houver um auto-relacionamento.

 

Outra alternativa, seria gerar um script de todos as PKs e FKs, excluir todas, executar os DELETEs e posteriormente rodar o script para recriá-las.

 

[ ]s,

 

Gustavo

 

Answer 10

Vlw pessoal, muito obrigado, mas consegui resolver meu problema...

O comendo que vc me passou funcionou Gustavo, msm que eu tenha que executar ela mais de uma vez por causa dos relacionamentos, mas resolve meu problema.

 

Muito obrigado pela ajuda Gustavo, e obrigado a todos que responderam, vcs são d mais.

 

Abraços!

Answer 11

Olá Tak,

 

Que bom que todos nós conseguimos em conjunto resolver o seu problema. Se o processo for executado manualmente, você pode adotar a solução de DELETE executada mais de uma vez. No entanto, se for executar várias vezes e de forma automatizada, a solução do Ricardo em conjunto com as demais pode ser interessante.

 

[ ]s,

 

Gustavo

 

Answer 12

 

Pessoal,

 

Juntando idéias de todos... saiu isso:

 

Code Block

EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSforeachtable 'DELETE FROM ?'

EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

 

 

 

Abraço!!

Answer 13

Salve pessoal, muito obrigado pela ajuda, Alexandre, funcionou perfeito, muito obrigado msm.

 

Um feliz ano novo pra todos e fiquem com Deus.

Abraços.

Answer 14

 

Blz TAK!!

 

Qualquer pode voltar aqui... Mas agora, acho que só no ano que vem!!! hehehe

 

 

Feliz ano novo!!!

 

 

Abraço