Bug script to delete the entire SQL SERVER database

3

I have the following script to drop EVERYTHING from the database, but in one of the database I have the processing that needs this script a bug happens, for some reason it can not run the first time I run, but in second wheel without any hindrance. 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) 

Errors shown are:  (Note: THE_TAB_NAME 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 only this error occurs in this database? Why would I run the script twice if it works?

Thank you for your attention!

    
asked by anonymous 19.12.2016 / 16:54

1 answer

0

The variable @COMANDO was 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 did the second time he would finish deleting the rest of the missing CTs and it worked. I was able to see this by doing a SELECT in @COMANDO . Changing @COMANDO to varchar(MAX) starts working regardless of bank size.

@Murilo that gave the idea of how to find the problem.

    
27.12.2016 / 22:21