Início / dba / Perguntas / 907
Accepted
Andrew Bickerton
Asked: 2011-01-27 05:03:46 +0800 CST

Como executar SQL em todos os bancos de dados em um servidor

  • 772

Eu tenho algum SQL padrão que executo em vários bancos de dados em um único servidor para me ajudar a diagnosticar problemas:

select 
    so.name,
    so.type,
    MAX(case when sc.text like '%remote%' then '' ELSE 'N' END) AS Relevant,
    @@ServerName as Server,
    DB_Name() as DBName 
from
    sysobjects so with (nolock)
    join syscomments sc with (nolock) on so.id = sc.id
where (sc.text like '%emote%')
group by so.name, so.type
order by so.type, so.name

Como posso executar isso em todos os bancos de dados em um único servidor? (além de conectar manualmente um de cada vez e executar)

sql-server

6 respostas

  1. Best Answer

    sp_MSForEachDB

    Uma opção é sp_MSForEachDB . Não está documentado, mas é útil, no entanto

    DECLARE @command varchar(1000) 
SELECT @command = 
    'USE [?] UPDATE Table1 SET Field1 = ''ninjas'' WHERE Field2 = ''pirates''' 
EXEC sp_MSforeachdb @command

    Uma pesquisa nas interwebs tem muitos outros exemplos também

    Nota: Sendo uma função não suportada (que tem alguns bugs conhecidos), você pode querer escrever sua própria versão (obrigado @Pradeep)

    O exemplo SQL acima precisaria ser reestruturado como:

    DECLARE @findKeySQL nvarchar(2000)
DECLARE @searchKey nvarchar(20)

SET @searchKey = lower('%remote%')

SET @findKeySQL = 'IF ''[?]'' NOT IN (''[master]'', ''[model]'', 
                                     ''[msdb]'', ''[tempdb]'')
        select 
            so.name,
            so.type,
            @@ServerName as Server,
            ''?'' as DBName 
        from
            [?].dbo.sysobjects so with (nolock)
            join [?].sys.all_sql_modules sc with (nolock) on so.id = sc.object_id
        where (lower(sc.definition) like ''' + @searchKey + ''')
        group by so.name, so.type
        order by so.type, so.name'

EXEC sp_MSForEachDB @findKeySQL

    NOTAS:

    1. ? é substituído na consulta como o nome do banco de dados, então estruture a consulta para definir explicitamente em qual banco de dados deve consultar
    2. modificado para usar sys.all_sql_modules como contém o texto completo do módulo (syscomments pode dividir a palavra-chave ao atingir a extensão das linhas)
    • 45

  2. Apenas meus $ 0,05: SQL Multi Script (execução de vários scripts em vários SQL Servers).

    • 9

  3. O SSMS Tools Pack faz isso muito bem e é gratuito para servidores de banco de dados anteriores a 2012. O recurso: "Executar em vários destinos" - http://www.ssmstoolspack.com/Features?f=6

    • 3

  4. Existe um outro método que dará saída em um único conjunto de resultados semi-mesclados. Primeiro abra Servidores Registrados e crie um novo grupo em Grupos de Servidores Locais e registre seu servidor uma vez para cada banco de dados, em cada caso definindo o banco de dados padrão para o desejado.

    Depois de concluído, clique com o botão direito do mouse em seu grupo e selecione Nova consulta. A janela de consulta que se abre terá "múltiplos" onde você normalmente veria um nome de servidor na barra de status. Quaisquer consultas executadas nesta janela funcionarão em cada servidor registrado que estava no grupo. A primeira coluna dos resultados será o nome do servidor registrado. O conjunto de resultados será fragmentado por essa primeira coluna e a ordem por testamentos só funcionará dentro desse fragmento.

    Funcionalidade muito poderosa, mas negligenciada, para quando você precisa executar rotineiramente o mesmo SQL em vários servidores.

    • 2

  5. Minha empresa desenvolveu uma ferramenta chamada xSQL Script Executor . É gratuito para uso pessoal e, para mim, facilitou muito a implantação de scripts em vários destinos.

    • 1

  6. Desenvolvi esta ferramenta: https://github.com/andreujuanc/TakoDeploy

    Ainda estou escrevendo algumas linhas sobre ele, mas está bastante estável agora. Eu o usei contra bancos de dados de produção e funciona como um encanto.

    • 0

relate perguntas