A Barebone Script To Generate SQL Script to Create Synonyms

This stored procedure can be used to generate a SQL script to create synonyms for all tables in a source database. Optionally, on the local SQL instance this SP can also create the synonyms for you, provided the target database and the schema (defaults to dbo and therefore can be omitted) already exists there. 

-- exec spGenerateSynonyms @sourcedb = 'AdminDBA', @targetdb = 'AdminDBA', @sourceschema= 'dbo', @targetschema='newschema', @printOnly = 1
/*
Description: 

Generates script to create synonyms in target db (usually, a blank db) for 
all tables that exist in source db (usually a subscriber db)

Requirements:
Source database and source schema (defaults to dbo so it can be omitted) 
must exist If parameter @PrintOnly is set to 0, meaning the script would  

also run the create synonyms statements then the  target database and 

schema must exist too... 

Caveats:
For now this procedure only generates scripts for tables. 

Synonyms on other types of objects (views, stored procedures etc.) are 

also supported by sql server.


Examples:
exec spGenerateSynonyms @sourcedb = 'EnterpriseSub', @targetdb = 'Enterprise', @printOnly = 1
exec spGenerateSynonyms @sourcedb = 'AdminDBA', @targetdb = 'Enterprise', @sourceschema='dbo',  @targetschema='myapp',  @printOnly = 1

exec spGenerateSynonyms @sourcedb = 'AdminDBA', @targetdb = 'Enterprise', @sourceSchema = 'appuser1', @printOnly = 1
exec spGenerateSynonyms @sourcedb = 'AdminDBA', @targetdb = 'AdminDBA', @targetschema='newschema', @printOnly = 0

exec spGenerateSynonyms @targetdb = 'ProductUserMaster' -- INVALID
exec spGenerateSynonyms @sourcedb = 'AdminDBA', @targetdb = 'AdminDBA'  -- INVALID, SOURCE AND TARGET  DATABASES + SCHEMAS CANNOT BE SAME

*/


set nocount on
go
use AdminDBA   -- Change this to the whatever database you are using to store DBA objects
go
if object_id('spGenerateSynonyms') is null
BEGIN
   PRINT 'Creating stored procedure spGenerateSynonyms'
   exec('create PROCEDURE spGenerateSynonyms as select '''' empty_string')
END
ELSE
   PRINT 'Stored procedure spGenerateSynonyms already exists.'
   PRINT 'Modifying stored procedure spGenerateSynonyms....'
GO
alter  PROCEDURE spGenerateSynonyms 
(
   @sourceDB sysname,                 -- aka base database where the objecgs are located
   @targetDB sysname,                 -- database where the synonyms will be created

   @object_type varchar(100) = 'Tables',   -- type of objects to create synonyms

   @sourceSchema sysname = 'dbo',     -- base schema name... defaults to dbo
   @targetSchema sysname = 'dbo',     -- defaults to dbo

   @PrintOnly bit = 1                 -- whether to also create the synonums or only print the SQL statements to create them
)


as
set nocount on

declare @sql varchar(4000), @def nvarchar(max)
declare @sql_string nvarchar(4000)

declare @sourceschema_id table(schema_id int)
declare @targetschema_id table(schema_id int)


select @sourcedb SourceDB, @sourceSchema SourceSchema, @targetdb TargetDB, @targetSchema TargetSchema

-- ************* VALIDATE INPUTS *********************
if @targetdb is null or @sourcedb is null
begin
   raiserror('Error: Source and target database names are required and cannot be null. Exiting...', 16, 1)
   return -1
end

if @targetdb  = @sourcedb and @sourceSchema = @targetSchema
begin
   raiserror('Error: Source and taget databases + schemas cannot be the same. Exiting...', 16, 1)
   return -1
end

if  db_id(@sourcedb) is null or  databasepropertyex(@sourcedb, 'Status')!='ONLINE'
begin
   raiserror('Error: Source database %s not found or not ONLINE. Exiting...', 16, 1, @sourcedb)
   return -1
end

-- If @PrintOnly=0 the SP will try to create the sysnonyms
-- Therefore the target db and the target schema must already exist
if @PrintOnly = 0 
BEGIN

		if  (db_id(@targetDB ) is null or  databasepropertyex(@targetDB , 'Status')!='ONLINE')
		begin
		   raiserror('Error: The target database %s not found or not ONLINE. Exiting...', 16, 1, @targetDB )
		   return -1
		end

		set @sql = 'select schema_id from [' + @targetDB + '].sys.schemas where name = ''' + @targetschema + ''''
		insert into @targetschema_id  exec (@sql)
		if not exists (select * from @targetschema_id)
		BEGIN
		   raiserror('Error: Target schema %s not found in the %s database . Exiting...', 16, 1, @targetSchema, @targetdb)
		   return -1
		END


END




set @sql = 'select schema_id from [' + @sourceDB + '].sys.schemas where name = ''' + @sourceSchema + ''''
insert into @sourceschema_id exec (@sql)
if not exists (select * from @sourceschema_id)
BEGIN
	raiserror('Error: Source schema %s not found in the %s database . Exiting...', 16, 1, @sourceschema, @sourceDB)
	return -1
END



if object_id('tempdb..#t1_synonyms_definitions') is not null
   drop table #t1_synonyms_definitions
create table #t1_synonyms_definitions(definition nvarchar(max))
set @sql = 'select  ''
Use [' + @Targetdb + ']
IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE schema_id = schema_id(''''' + @targetSchema + ''''') and name = N'''''' + NAME + '''''') 
CREATE SYNONYM [' + @targetSchema + '].['' + name + ''] FOR [' + @sourcedb + '].[' + @sourceSchema + '].['' + name  + '']'' from [' + @sourcedb + '].sys.tables'

if @sourceSchema is not null
set @sql = @sql + ' where schema_name(schema_id) = ''' + @sourceSchema + ''''

insert into #t1_synonyms_definitions exec (@sql)
select * from #t1_synonyms_definitions
declare c1 cursor for select * from #t1_synonyms_definitions
open c1
fetch c1 into @def 
while @@fetch_status = 0
begin
      print @def


      if @printOnly = 0 exec (@def)
      fetch c1 into @def 

end
close c1
deallocate c1

But why synonyms? Synonyms can mask/hide the underlying location of the base objects (think of linked server queries, cross database queries etc.),  regardless of who owns them and/or their names.  Or maybe you have two identical copies of the same app/code, one pointing to the live data and the other for the archive/historical data.

You might be wondering, well views can achieve the same and I can add some logic to make them even useful. And you would be right, views are more useful and certainly has more use cases. But if you are creating views only to mask the underlying location of tables or their names, then I would say synonyms are better because for one thing, the views would not automatically reflect changes to the underlying tables structures. Of course then there are good use cases where you don't want a view to exactly reflect the underlying table structure.