Script: Criar sinonimos para todas as tabelas de usuário

Um synonym (ou sinônimo) de banco de dados é um objeto utilizado para servir nos seguintes propósitos:
- Fornecer um nome alternativo para outro objeto de banco de dados, referindo-se a um objeto de base que pode ser existente no servidor local ou num servidor remoto;
- Fornecer uma camada de abstração que “protege” uma aplicação client de mudanças no nome ou locação do objeto base.
Na prática, seria como um “alias”, um “apelido” – de uma forma genérica e grosseira. Pode ser bem útil quando você utiliza um linked server e quer “encurtar” a cláusula “from”.
Escreverei num próximo artigo o que vem a ser um “linked server” com maiores detalhes, mas para entender melhor e simplificando o artigo, resumo de forma superficial que o “linked server” é um tipo de conexão remota a outro servidor de banco de dados.
Normalmente você faz uma query simples usando no “from” o nome da tabela. Se a tabela estiver em outro banco de dados, você utiliza “banco.schema.tabela”. Por exemplo:
select campo from tabela
ou
select campo from banco.schema.tabela
Até aí, tudo bem. Inclusive aqui já posso exemplificar como usar dentro do mesmo servidor de banco de dados o sinônimo Por exemplo, no ultimo codigo, ao invés de escrever “banco.schema.tabela”, voce pode escrever apenas “meuSinonimo”.
select campo from meuSinonimo
Num linked server, você informa “servidor.banco.schema.tabela”. Da mesma forma que expliquei acima, voce pode trocar tudo isto por apenas um “sinônimo”.
O comando básico para criar um sinônimo é este:
use <banco>; 'Banco onde voce vai USAR o sinonimo em queries go create synonym <meuSinonimo> for servidor.banco.schema.tabela;
Então, ao invés de se referenciar a “servidor.banco.schema.tabela”, voce apenas se referencia ao nome que quiser (sinônimo). Seu código fica mais limpo.
Vamos mais longe nesta análise, e é aí que entra o contexto deste artigo. Imagine se você tiver que criar um sinônimo pra cada tabela do seu banco de dados! E se houver umas 500, 1.000 tabelas?
Neste caso, crie uma stored procedure para geração automática de todos sinônimos, um pra cada tabela.
Esta stored procedure faz primeiro o drop de todos sinônimos e depois cria tudo de novo. É interessante, pois você pode executar quantas vezes quiser, por exemplo, se houver alguma(s) tabela(s) nova(s) e que ainda não tem um “sinônimo”.
Outra particularidade: aqui eu sugiro usar um prefixo pro sinônimo: ao bater o olho no script, você sabe que ali há uma referencia de um sinônimo, não corre o risco de ter um sinônimo de uma tabela que já existe com o mesmo nome.
CREATE PROCEDURE GeraSinonimos as DECLARE @dynsqlcmd VARCHAR(250) DECLARE @tblname VARCHAR(150) DECLARE @synname VARCHAR(150) DECLARE @cnter INT -- Drop all synonyms DECLARE dbcursor CURSOR FOR SELECT sysobjects.name AS 'table name' FROM sysobjects WHERE sysobjects.type = 'sn' OPEN dbcursor FETCH NEXT FROM dbcursor INTO @synname WHILE (@@FETCH_STATUS =0) BEGIN SET @dynsqlcmd = 'DROP SYNONYM [' + @synname + ']' EXEC (@dynsqlcmd) FETCH NEXT FROM dbcursor INTO @synname END CLOSE dbcursor DEALLOCATE dbcursor -- Create Synonym DECLARE tblnmcursor CURSOR FOR SELECT sysobjects.name AS 'table name' FROM CMSQNXTTESTSQL.PlanData_Dev.dbo.sysobjects WHERE sysobjects.name <> 'dtproperties' AND sysobjects.name <> 'sysdiagrams' AND sysobjects.type IN ('u','v') OPEN tblnmcursor FETCH NEXT FROM tblnmcursor INTO @tblname WHILE (@@FETCH_STATUS =0) BEGIN SET @synname = 'Any Prefix' + @tblname SET @dynsqlcmd = 'CREATE SYNONYM ' + @synname + ' FOR [Server Name].[Database Name].dbo.[' + @tblname + ']' EXEC (@dynsqlcmd) FETCH NEXT FROM tblnmcursor INTO @tblname END CLOSE tblnmcursor DEALLOCATE tblnmcursor
Pronto. Dessa forma você tem um procedimento automático de geração de sinônimos.
Já precisou de algo assim e nunca utilizou? Alguma experiência prática do caso?
Deixe uma resposta