--\
---) Author: Hans Michiels
---) Creates synonyms for tables, views, user defined functions
---) and stored procedures in a different database.
---) All synonyms in the specified schema are dropped
---) prior to (re)creation.
--/
/*
(c) Copyright 2016 - hansmichiels.com
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program. If not, see http://www.gnu.org/licenses/.
*/
USE [AdventureWorks2014] -- Replace with database you want to create the synonyms in.
GO
--\--------------------------------------------------------------------------
---) DECLARATIONS
--/--------------------------------------------------------------------------
DECLARE @SourceDatabase SYSNAME
DECLARE @SourceSchema SYSNAME
DECLARE @SynonymsSchema SYSNAME
DECLARE @SynonymsPrefix NVARCHAR(128)
DECLARE @SynonymName SYSNAME
DECLARE @CurSchemaName SYSNAME
DECLARE @CurObjectName SYSNAME
DECLARE @CurObjectType CHAR(2)
DECLARE @nsql NVARCHAR(4000)
DECLARE @DebugPrint NVARCHAR(500)
DECLARE @SimulationMode CHAR(1)
--\--------------------------------------------------------------------------
---) CONFIGURATION: adjust the variable values to meet your needs.
--/--------------------------------------------------------------------------
SELECT @SourceDatabase = 'AW2014_Staging' -- Database in which objects exist
-- for which synonyms must be created.
, @SourceSchema = 'stg' -- Schema name of source objects.
, @SynonymsSchema = 'stg' -- Schema name to put synonyms in.
, @SynonymsPrefix = '' -- Optional Synonyms name prefix.
, @SimulationMode = 'Y' -- Use Y if you only want the SQL statement in the
-- output window without it being executed.
--\--------------------------------------------------------------------------
---) INITIALIZATION
--/--------------------------------------------------------------------------
IF NOT EXISTS(SELECT 1 FROM sys.schemas WHERE name = @SynonymsSchema)
BEGIN
SELECT @nsql ='CREATE SCHEMA ' + QUOTENAME(@SynonymsSchema) + ' AUTHORIZATION [dbo];';
IF @SimulationMode = 'Y' PRINT '----- SIMULATION MODE: SCRIPT IS NOT EXECUTED ------ ';
PRINT @nsql;
IF @SimulationMode != 'Y' EXECUTE sp_executesql @nsql;
END
--\--------------------------------------------------------------------------
---) MAIN PART I: Drop all existing synonyms in the specified schema.
--/--------------------------------------------------------------------------
DECLARE ExistingObjCursor CURSOR LOCAL STATIC FOR
SELECT syn.name AS [SynonymName]
FROM sys.synonyms syn
JOIN sys.schemas sch
ON syn.schema_id = sch.schema_id
WHERE sch.name = @SynonymsSchema
IF @SimulationMode = 'Y' PRINT '----- SIMULATION MODE: SCRIPT IS NOT EXECUTED ------ ';
SET @DebugPrint = '-- Dropping all existing synonyms in schema ' + QUOTENAME(@SynonymsSchema)
PRINT REPLICATE('-', LEN(@DebugPrint))
PRINT @DebugPrint
PRINT REPLICATE('-', LEN(@DebugPrint))
OPEN ExistingObjCursor
FETCH NEXT FROM ExistingObjCursor INTO @SynonymName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @nsql = 'IF OBJECT_ID(''' + QUOTENAME(@SynonymsSchema) + '.' + QUOTENAME(@SynonymName) + ''', ''SN'') IS NOT NULL'
+ ' DROP SYNONYM ' + QUOTENAME(@SynonymsSchema) + '.' + QUOTENAME(@SynonymName) + ';'
PRINT @nsql;
IF @SimulationMode != 'Y' EXECUTE sp_executesql @nsql;
FETCH NEXT FROM ExistingObjCursor INTO @SynonymName
END
CLOSE ExistingObjCursor
DEALLOCATE ExistingObjCursor
--\--------------------------------------------------------------------------
---) MAIN PART II: Recreate all synonyms in the specified schema.
--/--------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#SourceObjects') IS NOT NULL BEGIN
DROP TABLE #SourceObjects
END
CREATE TABLE #SourceObjects
( [SchemaName] SYSNAME NOT NULL
, [ObjectName] SYSNAME NOT NULL
, [ObjectType] CHAR(2) NOT NULL
)
-- Insert USER objects
SELECT @nsql =
'INSERT INTO #SourceObjects([SchemaName], [ObjectName], [ObjectType])'
+ ' SELECT sch.name AS [SchemaName]'
+ ' , obj.name AS [ObjectName]'
+ ' , obj.type AS [ObjectType]'
+ ' FROM [' + @SourceDatabase + '].sys.objects obj'
+ ' JOIN [' + @SourceDatabase + '].sys.schemas sch'
+ ' ON sch.schema_id = obj.schema_id '
+ ' WHERE sch.name = ''' + @SourceSchema + ''''
+ ' AND obj.type IN ( '
+ ' ''FN'' /* SQL_SCALAR_FUNCTION */ '
+ ' , ''P '' /* SQL_STORED_PROCEDURE */ '
+ ' , ''IF'' /* SQL_INLINE_TABLE_VALUED_FUNCTION */ '
+ ' , ''TF'' /* SQL_TABLE_VALUED_FUNCTION */ '
+ ' , ''U '' /* USER_TABLE */ '
+ ' , ''V '' /* VIEW */ '
+ ' )'
+ ' ORDER BY obj.type, sch.name, obj.name'
PRINT '/*'
EXECUTE sp_executesql @nsql;
PRINT '*/'
DECLARE ObjectCursor CURSOR LOCAL STATIC FOR
SELECT [SchemaName], [ObjectName], [ObjectType]
FROM #SourceObjects
IF @SimulationMode = 'Y' PRINT '----- SIMULATION MODE: SCRIPT IS NOT EXECUTED ------ ';
SET @DebugPrint = '-- Recreate synonyms in schema ' + QUOTENAME(@SynonymsSchema)
PRINT REPLICATE('-', LEN(@DebugPrint))
PRINT @DebugPrint
PRINT REPLICATE('-', LEN(@DebugPrint))
OPEN ObjectCursor
FETCH NEXT FROM ObjectCursor INTO @CurSchemaName, @CurObjectName, @CurObjectType
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SynonymName = @SynonymsPrefix + @CurObjectName
SET @nsql = 'CREATE SYNONYM ' + QUOTENAME(@SynonymsSchema) + '.' + QUOTENAME(@SynonymName)
+ ' FOR ' + QUOTENAME(@SourceDatabase) + '.' + QUOTENAME(@CurSchemaName) + '.' + QUOTENAME(@CurObjectName) + ';'
PRINT @nsql;
IF @SimulationMode != 'Y' EXECUTE sp_executesql @nsql;
FETCH NEXT FROM ObjectCursor INTO @CurSchemaName, @CurObjectName, @CurObjectType
END
CLOSE ObjectCursor
DEALLOCATE ObjectCursor
GO
--\--------------------------------------------------------------------------
---) CLEAN UP
--/--------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#SourceObjects') IS NOT NULL BEGIN
DROP TABLE #SourceObjects
END