Create synonym to all tables in a different database with a script

Question

Is there an easy way to create synonyms to all tables in a different database?

thanks

EDIT: I have a number of stored procedures that hardcoded some table schemas into the select queries. When I copy the schemas to a new server, the SPs fail because the schema doesn't exist. There is little control I have over the destination server and I don't want to having to change all the SP, so I thought synonym may be a good solution.

Answer 1

You could run a query like this on the original database, then run the output results on your new database.

select 'create synonym syn_' + t.name + ' for [' + DB_NAME() + '].[' + s.name + '].[' + t.name + ']' 
    from sys.tables t
        inner join sys.schemas s
            on t.schema_id = s.schema_id
    where t.type = 'U'

As an example, running this against the Master database would produce:

create synonym syn_spt_fallback_db for [master].[dbo].[spt_fallback_db]
create synonym syn_spt_fallback_dev for [master].[dbo].[spt_fallback_dev]
create synonym syn_spt_fallback_usg for [master].[dbo].[spt_fallback_usg]
create synonym syn_spt_monitor for [master].[dbo].[spt_monitor]
create synonym syn_spt_values for [master].[dbo].[spt_values]
create synonym syn_MSreplication_options for [master].[dbo].[MSreplication_options]