A Barebone Script To Generate SQL Script to Create Synonyms

-- 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