Synonyms

tags: #SQLServer
categories: SQLServer

Автоматически создаем синонимы для всех таблиц целевой БД

TargetDB - база в которой будут созданы синонимы
SourceDB - база на которую синонимы будут ссылаться
LinkedServer - если источник реализован через связанный сервер, иначе оставляем пустым

USE [TargetDB]
GO

DECLARE
@databaseName sysname = 'SourceDB',
@LinkedServer varchar(20) = 'ip or name or empty'

DECLARE
@TSQL nvarchar(mSYN) = N'',
@server varchar(30) = ''

IF(@LinkedServer is not null and @LinkedServer <> '')
    SET @server = '[' + @LinkedServer + '].'

IF OBJECT_ID('tempdb..#DBSynonym') IS NOT NULL
    DROP TABLE #DBSynonym;

CREATE TABLE #DBSynonym( 
    [TABLENAME] sysname NOT NULL
)

SET @TSQL = N' INSERT INTO #DBSynonym ([TABLENAME]) SELECT TABLE_NAME FROM ' + @server + '[' + @databaseName + '].INFORMATION_SCHEMA.TABLES GO'

EXEC (@TSQL)

DECLARE @TABLENAME sysname

WHILE EXISTS ( SELECT TOP 1 1 FROM #DBSynonym )
BEGIN
    SELECT TOP 1 @TABLENAME = TABLENAME FROM #DBSynonym
    
    SET @TSQL = N''
    SET @TSQL =
    N' IF EXISTS (SELECT * FROM sys.synonyms WHERE name = ''SYN_' + @TABLENAME + ''') DROP SYNONYM [SYN_'+ @TABLENAME + '];
    CREATE SYNONYM ' + N'.' + QUOTENAME('SYN_'+@TABLENAME) + N' FOR ' + @server + QUOTENAME(@databaseName) + N'.dbo.' + QUOTENAME(@TABLENAME) + N';'

    EXEC sp_executesql @TSQL

    DELETE FROM #DBSynonym
    WHERE TABLENAME = @TABLENAME
END