Create a synonym for all tables in another database using script

Question

Is there an easy way to create synonyms for all tables in another database?

thanks

EDIT: I have several stored procedures that hard-coded some table schemas in selected queries. When I copy the schemas to the new server, the SPs fail because the schema does not exist. There is little control on the destination server, and I don’t want to change all the SPs, so I thought a synonym might be a good solution.

Answer 1

You can run such a query in the original database, and then run the output in 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 method against the Master database will create:

 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] 

Answer 2

Create a stored procedure something like this:

 CREATE PROCEDURE SynonymUpdate @Database nvarchar(256), -- such as 'linkedserver.database' or just 'database' @Schema sysname -- such as 'dbo' AS CREATE TABLE #Tables ( TableID int identity(1,1) NOT NULL PRIMARY KEY CLUSTERED, Table_Name sysname ) DECLARE @SQL nvarchar(4000), @ID int SET @SQL = N'SELECT Table_Name FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE Table_Schema = @TableSchema' INSERT #Tables EXEC sp_executesql @SQL, N'@TableSchema sysname', @Schema SELECT @ID = MAX(TableID) FROM #Tables WHILE @ID > 0 BEGIN SELECT @SQL = 'CREATE SYNONYM ' + Table_Name + ' FOR ' + @Database + '.' + @Schema + '.' + Table_Name FROM #Tables WHERE TableID = @ID PRINT @SQL --EXEC sp_executesql @SQL SET @ID = @ID - 1 END 

Then run it like this:

 EXEC SynonymUpdate 'Database' , 'dbo' 

Note that you must run this as a user with the privilege of creating synonyms. If you want the user to run it without these privileges, SQL 2000 is out of luck, in SQL 2005 you can place the EXECUTE AS clause there.

Answer 3

Is the other database on a separate server (or instance) or are both databases stored in the instance?

If it is one instance , and both databases are on it, use three names:

 SELECT * FROM database1.dbo.table_X JOIN database2.dbo.table_Y ... 

If another database exists on a separate instance of SQL Server (you can have more than one SQL Server in a field) or the database exists on an instance of SQL Server in another field / VM - create a Linked Server . Then you use four notation:

 SELECT * FROM database1.dbo.table_X JOIN linked_server_name.database2.dbo.table_Y