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?