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!