Início / dba / Perguntas / 255011
Accepted
Saeed Neamati
Asked: 2019-12-07 02:33:00 +0800 CST

Como descartar um procedimento em muitos bancos de dados (SQL Server 2016)

  • 772

Eu tenho um procedimento simples que existe em mais de 150 bancos de dados. Eu posso excluí-los um por um, que é o trabalho mais estúpido a se fazer no mundo do desenvolvimento de software. Ou posso de alguma forma excluí-los dinamicamente em uma consulta, o que é inteligente, mas não funciona.

Eu tentei:

execute sp_msforeachdb 'drop procedure Usages'

Eu obtive:

Não é possível descartar o procedimento 'Usages', porque ele não existe ou você não tem permissão.

Então pensei que talvez eu criasse um cursor para isso. Assim escrevi:

declare @command nvarchar(max) = '
drop procedure Usages
';
declare @databaseName nvarchar(100)
declare databasesCursor cursor for select [name] from sys.databases where database_id > 5
open databasesCursor
fetch next from databasesCursor into @databaseName
while @@fetch_status = 0
begin
    print @databaseName
    execute sp_executesql @command
    fetch next from databasesCursor into @databaseName
end
close databasesCursor
deallocate databasesCursor

Mais uma vez, recebi a mesma mensagem. Eu pensei, talvez isso não mude o contexto do banco de dados, então eu prefixei o comando delete com o nome do banco de dados fornecido, para que os comandos se tornassem semelhantes a drop procedure [SomeDatabase].dbo.Usages, mas recebi:

'DROP PROCEDURE' não permite especificar o nome do banco de dados como um prefixo para o nome do objeto.

Então, eu descobri para executar dinamicamente use @databaseNamepara que eu possa descartar o procedimento no contexto desse banco de dados. Mas não está funcionando. O que devo fazer? Estou preso ao método inteligente e passei mais tempo do que o método estúpido agora.

sql-server sql-server-2016

2 respostas

  1. Best Answer

    Você estava muito perto, mas esqueceu uma coisinha:

    execute sp_msforeachdb 'USE [?] drop procedure Usages'

    O uso [?] realmente irá para cada db e o executará lá, isso deve resolver seu problema.
    No entanto, você pode adicionar algo que primeiro verifique se o procedimento existe antes de eliminá-lo, porque agora você ainda receberá o erro se ele não existir no banco de dados.

    Edit: Uma versão mais limpa seria esta:

    DECLARE @SQL NVARCHAR(MAX)

SET @SQL = '
USE [?]
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N''Usages'')
                    AND type IN ( N''P'', N''PC'' ) ) 
drop procedure Usages
'

EXEC sp_MSforeachdb @SQL

    Ele verificará se existe antes de cair, então se houver tal sp, não lhe dará um erro.

    EDIT2: ElliotMajor fez o comentário

    Eu observaria que sp_msforeachdb não está documentado e não deve ser usado. Muitos bugs conhecidos

    E ele está certo sobre isso. Para o que você está fazendo agora, deve estar tudo bem, mas se você precisar colocar isso em algum código de produção, há algumas substituições para este sp.

    Versões melhoradas por: Spaghetti DBA e Aaron Bertrand

    • 5

  2. Faltam algumas informações, qual versão do SQL Server você está usando? Quais permissões você tem?

    Dito isso, acho que o código abaixo deve funcionar, testei no SQL 2019.

    Isso cria uma consulta para cada instrução drop, em seguida, executa cada uma e a executa (e imprime o erro se falhar)

    Você pode alterar o EXEC (@query)para PRINT @Queryse quiser apenas as instruções ou se livrar do LOOP e apenasSELECT * FROM #querytable

    GO

DECLARE @procedure_name SYSNAME,
        @query      NVARCHAR(MAX);

SELECT @procedure_name  = 'usages'

SELECT 'USE ' + QUOTENAME(NAME) +
       '; DROP PROCEDURE [dbo].' + QUOTENAME(@procedure_name) + ';' AS Query,
       0 AS Completed
INTO   #querytable
FROM   sys.databases
WHERE  database_id > 4
       AND state_desc = 'ONLINE'

WHILE (SELECT COUNT(*) FROM #querytable WHERE Completed = 0) > 0
BEGIN
    SELECT TOP(1) @query = query FROM #querytable WHERE Completed = 0
    BEGIN TRY
        EXEC (@query)
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE()
    END CATCH
    UPDATE #querytable SET Completed = 1 WHERE Query = @query
END

DROP TABLE #querytable
    • 2

relate perguntas