Início / dba / Perguntas / 153511
Accepted
Connor
Asked: 2016-10-28 09:51:04 +0800 CST

Localize e substitua o nome do banco de dados em exibições e procedimentos armazenados

  • 772

Estou em uma situação difícil. Existem mais de 1.000 exibições e procedimentos armazenados em um punhado de bancos de dados que são codificados para ir atrás de outro banco de dados.

No meu exemplo, os nomes dos bancos de dados no SQL Server são HOST_1e LOYALTY_1. As visualizações e procedimentos estão tentando seguir HOST_PRODe LOYALTY_PRODnas instruções FROMe JOIN. Existe alguma maneira que eu possa fazer uma localização global e substituir em todos os modos de exibição e procedimentos para substituir o PRODcom 1?

Deve haver uma maneira mais fácil do que clicar com o botão direito do mouse em cada um, criar o script de alteração para uma nova janela, alterar o texto e pressionar F5. Mesmo se eu pudesse criar todas as alterações necessárias e executá-las de uma vez, isso funcionaria.

stored-procedures sql-server-2012

2 respostas

  1. Best Answer

    Isso pode ser feito usando a tabela sys.objects e a função OBJECT_DEFINITION(). 

    SELECT OBJECT_DEFINITION(id) AS ORIGINAL, 
    REPLACE(OBJECT_DEFINITION(id),'VALUE_TO_BE_REPLACED', 'VALUE_TO_REPLACE_WITH') AS UPDATED
    ,*
FROM sys.sysobjects o
WHERE xtype = 'V'

    A coluna 'UPDATED' desta consulta pode ser usada para fazer uma localização rápida e substituir qualquer nome de banco de dados ou tabela que possa ser encontrado na definição de visualização. Você deve certificar-se de fazer o mesmo para todas as versões possíveis do texto que está procurando. Por exemplo, um banco de dados chamado 'db' pode ser escrito como [db] ou db. Depois de obter o que deseja da consulta, basta copiar e colar em uma janela de consulta e executar tudo. Certifique-se de fazer backup do banco de dados antes de executá-lo. Também revise cuidadosamente o texto de saída para que você possa verificar se a substituição funcionou corretamente e não mudou acidentalmente mais do que o pretendido.

    BTW, a resposta que WEI_DBA deu é perfeitamente aceitável também. É apenas uma abordagem diferente para a mesma coisa. Em seu exemplo, a GUI ajudará a obter todas as definições de objeto para a janela de consulta. Então você apenas usaria Find/Replace(ctrl+h) para substituir o nome do banco de dados. Cada um na sua neste caso.
    Não sei se há uma vantagem em qualquer uma das abordagens, exceto talvez que minha abordagem permita limitar a consulta apenas a determinados objetos (visualizações neste caso) com filtros adicionais adicionados, se necessário, na cláusula where. Gerar scripts permitirá que você filtre apenas exibições, procedimentos armazenados, etc. e, em seguida, selecione objetos individualmente, mas os filtros adicionais serão limitados a tudo o que a interface do usuário pode fazer.

    • 4

  2. O seguinte provavelmente forneceria o que você precisa:

    SELECT 
    '-------------------------------------'  + CHAR(13) + CHAR(10) +
    'DROP ' + IIF(o.type = 'P', 'PROCEDURE', 'VIEW') + ' [' + s.name + '].[' + o.name + ']'  + CHAR(13) + CHAR(10) +
    'GO' + CHAR(13) + CHAR(10) +
    REPLACE(REPLACE([definition],'HOST_PROD','HOST_1'),'LOYALTY_PROD','LOYALTY_1') + CHAR(13) + CHAR(10) +
    'GO' + CHAR(13) + CHAR(10)
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.[type] IN ('P','V')
AND [definition] LIKE '%HOST_PROD%' OR [definition] LIKE '%LOYALTY_PROD%'

    Isso obterá apenas os procedimentos necessários, os descartará antes de recriá-los e contabiliza procedimentos/visualizações com nomes engraçados. Você pode copiar o resultado e colar em uma nova janela de consulta. Parece funcionar bem no meu sistema de teste.

    Certifique-se de revisar isso primeiro, porém, você é responsável por seu banco de dados e qualquer dano causado!

    • 2

relate perguntas