Bug script to delete the entire SQL SERVER database

Question:

I have the script below to drop EVERYTHING from the database, but in one of the databases I have the processing that needs this script there is a bug, for some reason it can't run the first time I run it, but on the second run without no impediment. follow 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 be causing this error? Why is this error only in this database? Why if I run the script twice it works?

Thanks for listening!

Answer:

The variable @COMANDO was varchar(8000) , but the amount of CONSTRAINT made the command burst that size, because of that the last line was cut off. Then, if I ran it the second time, it would finish deleting the rest of the CTs that were missing and it worked. I could see this doing a SELECT in @COMANDO . If you change @COMANDO to varchar(MAX) it works regardless of the size of the bank.

@Murilo who gave the idea of ​​how to find the problem.

Post Views: 0
Scroll to Top