Bug script to delete entire SQL SERVER database

Asked

Viewed 178 times

3

I have the script below to drop EVERYTHING from the database, but in one of the database I have the processing that needs this script happens a bug, for some reason it can not run the first time I run, but in the second wheel without any impediment. Follows script

DECLARE @ALT_TAB varchar(250)
DECLARE @COMANDO varchar(8000)


/***********  DROP CONSTRAINTS  ********************/
SET  @COMANDO = ''
DECLARE vendor_cursor CURSOR FOR
  SELECT ('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME+ '] DROP CONSTRAINT [' + CONSTRAINT_NAME + '];') as ALT
  FROM information_schema.table_constraints
  WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' 

OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor INTO @ALT_TAB

WHILE @@FETCH_STATUS = 0
 BEGIN
  IF ISNULL(@ALT_TAB, '') <> ''
  SET @COMANDO = @COMANDO + ISNULL(@ALT_TAB, '') + CHAR(13)

  FETCH NEXT FROM vendor_cursor INTO @ALT_TAB
 END

CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

EXEC(@COMANDO) 
/**********************************************************/




/***********  DROP TABLES  **********************/
SET  @COMANDO = ''
DECLARE tables_cursor CURSOR FOR
 SELECT 'DROP TABLE ' + TABLE_NAME + ';' 
 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME <> 'sysdiagrams'
 ORDER BY TABLE_NAME

OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @ALT_TAB

WHILE @@FETCH_STATUS = 0
 BEGIN
  IF ISNULL(@ALT_TAB, '') <> ''
  SET @COMANDO = @COMANDO + ISNULL(@ALT_TAB, '') + CHAR(13)

  FETCH NEXT FROM tables_cursor INTO @ALT_TAB
 END

CLOSE tables_cursor;
DEALLOCATE tables_cursor;

EXEC(@COMANDO)

The errors presented are: (Note: NOME_TABELA is always the name of a different table)

Mensagem 105, Nível 15, Estado 1, Linha 1
Unclosed quotation mark after the character string 'CT_ENTIDADE_PRINCIPAL'.
Mensagem 102, Nível 15, Estado 1, Linha 1
Incorrect syntax near 'CT_ENTIDADE_PRINCIPAL'.
Mensagem 3726, Nível 16, Estado 1, Linha 1
Could not drop object 'NOME_TABELA' because it is referenced by a FOREIGN KEY constraint.
Mensagem 3726, Nível 16, Estado 1, Linha 1
Could not drop object 'NOME_TABELA' because it is referenced by a FOREIGN KEY constraint.
Mensagem 3726, Nível 16, Estado 1, Linha 1
Could not drop object 'NOME_TABELA' because it is referenced by a FOREIGN KEY constraint.
Mensagem 3726, Nível 16, Estado 1, Linha 1
Could not drop object 'NOME_TABELA' because it is referenced by a FOREIGN KEY constraint.
Mensagem 3726, Nível 16, Estado 1, Linha 1
Could not drop object 'NOME_TABELA' because it is referenced by a FOREIGN KEY constraint.
Mensagem 3726, Nível 16, Estado 1, Linha 1
Could not drop object 'NOME_TABELA' because it is referenced by a FOREIGN KEY constraint.
Mensagem 3726, Nível 16, Estado 1, Linha 1
Could not drop object 'NOME_TABELA' because it is referenced by a FOREIGN KEY constraint.
Mensagem 3726, Nível 16, Estado 1, Linha 1
Could not drop object 'NOME_TABELA' because it is referenced by a FOREIGN KEY constraint.
Mensagem 3726, Nível 16, Estado 1, Linha 1
Could not drop object 'NOME_TABELA' because it is referenced by a FOREIGN KEY constraint.
Mensagem 3726, Nível 16, Estado 1, Linha 1
Could not drop object 'NOME_TABELA' because it is referenced by a FOREIGN KEY constraint.
Mensagem 3726, Nível 16, Estado 1, Linha 1
Could not drop object 'NOME_TABELA' because it is referenced by a FOREIGN KEY constraint.
Mensagem 3726, Nível 16, Estado 1, Linha 1
Could not drop object 'NOME_TABELA' because it is referenced by a FOREIGN KEY constraint.
Mensagem 3726, Nível 16, Estado 1, Linha 1
Could not drop object 'NOME_TABELA' because it is referenced by a FOREIGN KEY constraint.
Mensagem 3726, Nível 16, Estado 1, Linha 1
Could not drop object 'NOME_TABELA' because it is referenced by a FOREIGN KEY constraint.
Mensagem 3726, Nível 16, Estado 1, Linha 1
Could not drop object 'NOME_TABELA' because it is referenced by a FOREIGN KEY constraint.
Mensagem 3726, Nível 16, Estado 1, Linha 1
Could not drop object 'NOME_TABELA' because it is referenced by a FOREIGN KEY constraint.
Mensagem 3726, Nível 16, Estado 1, Linha 1
Could not drop object 'NOME_TABELA' because it is referenced by a FOREIGN KEY constraint.

What could possibly be in this error? Why only this error occurs in this database? Because if I run twice the script it works?

Thank you for your attention!

sql sql-server script cursor
  • 1

    By the way it is name of some object that is generating the error in the SQL string to be executed. Here it ran. Comments the line that gives the exec and replace it with select @COMMAND to know the generated SQL then paste it into management studio to see if it has syntax error.

  • There is! I found it! The @COMMAND variable was varchar(8000), but the amount of CONSTRAINT caused the command to pop this size, so the last line was cut. So if I executed the second time he would finish deleting the rest of the missing Cts and it would work. I could see it doing what you said about giving SELECT in COMMAND. I changed the COMMAND to varchar(MAX) and now everything is fine. If you want, compile this that I wrote in a reply that I give you her credits! Thanks for the help!

  • 1

    I think you can answer for yourself, the biggest job was yours. But I’m glad to know that I helped. I don’t know if this would be right to do.

1 answer

0


The variable @COMANDO era varchar(8000), but the amount of CONSTRAINT caused the command to burst this size, because of that the last line was cut. So if I ran the second time he would finish deleting the rest of the missing Cts and it worked. I could see this by doing a SELECT on @COMANDO. If you change the @COMANDO for varchar(MAX) it works independently of bank size.

@Murilo who gave the idea of how to discover the problem.

Browser other questions tagged sql sql-server script cursor

You are not signed in. Login or sign up in order to post.