none
Procedure para deletar dados de todas as tabelas do bd RRS feed

  • Pergunta

  • 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.

    sexta-feira, 28 de dezembro de 2007 16:33

Respostas

  • 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

    sexta-feira, 28 de dezembro de 2007 17:15
  •  

    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!!

    sexta-feira, 28 de dezembro de 2007 19:17

Todas as Respostas

  • 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

    sexta-feira, 28 de dezembro de 2007 16:40
  • 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,

    sexta-feira, 28 de dezembro de 2007 16:46
  • 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

     

     

     

     

    sexta-feira, 28 de dezembro de 2007 17:07
  • 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

    sexta-feira, 28 de dezembro de 2007 17:15
  •  

    Gustavo,

     

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

     

     

    Abraço!!

    sexta-feira, 28 de dezembro de 2007 17:53
  • Ricardo vlw, mas se as tabelas tiverem relacionamento ele naum deleta, pq possui um relacionamento.

    Tem alguma maneira de modifiar isso??

    sexta-feira, 28 de dezembro de 2007 17:54
  • 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,

     

    sexta-feira, 28 de dezembro de 2007 18:00
  • 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
    sexta-feira, 28 de dezembro de 2007 18:03
  • 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

     

    sexta-feira, 28 de dezembro de 2007 18:05
  • 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!

    sexta-feira, 28 de dezembro de 2007 18:13
  • 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

     

    sexta-feira, 28 de dezembro de 2007 18:23
  •  

    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!!

    sexta-feira, 28 de dezembro de 2007 19:17
  • Salve pessoal, muito obrigado pela ajuda, Alexandre, funcionou perfeito, muito obrigado msm.

     

    Um feliz ano novo pra todos e fiquem com Deus.

    Abraços.

    sexta-feira, 28 de dezembro de 2007 20:11
  •  

    Blz TAK!!

     

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

     

     

    Feliz ano novo!!!

     

     

    Abraço

    sexta-feira, 28 de dezembro de 2007 21:02