10

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.

Haoest
  • 13,610
  • 29
  • 89
  • 105
  • Doesn't answer my database setup question. When you access either database, is the connection string identical other than database/catalog? Are the hostname/IP and port the same? – OMG Ponies Aug 25 '10 at 19:39
  • If the SPs fail for lack of schema, why not create the missing schema? But can the SPs run in the new setup - tables exist, etc? – OMG Ponies Aug 25 '10 at 19:40
  • It's sort of a long story. The development is meant to build on a retail application, which uses its own custom schema chosen at the time of set up. So the extension, which I inherited, uses SP which refers to something like retailProduct.table1. The problem is this schema is different from dev to production. So I am curious if it's better to create a synonym for each retailProduct.tablex. It woulda been the exact case of a linked server if the extension data is created in a separate database. But it's not. The tables and SPs are integrated into to same DB as the retail product. – Haoest Aug 25 '10 at 19:51
  • A custom schema would be fine...if the setup iterates over all references to keep them in sync. Still haven't gotten a definitive answer regarding setup, but doesn't sound like Linked Servers are the solution... – OMG Ponies Aug 25 '10 at 19:57

3 Answers3

29

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]
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
10

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 have to run this as a user with the privilege of creating synonyms. If you want a user without those privileges to run it, in SQL 2000 no luck, in SQL 2005 you can put an EXECUTE AS clause in there.

ErikE
  • 48,881
  • 23
  • 151
  • 196
  • 1
    +1 for this SP approach. @ErikE, when you call it again to reflect new updates, you will get into problems. I.e., you need to use DROP and CREATE whenever you want to change a synonym; – xpt Jul 02 '13 at 15:01
  • @xpt Absolutely true. `DROP` if already existing is required. An exercise for the reader. – ErikE Jul 03 '13 at 04:21
  • Added the drop statement for the busy readers out there :P `SELECT @SQL = 'IF EXISTS ( SELECT * FROM sys.synonyms WHERE name = ''' + Table_Name + ''' AND OBJECT_ID(base_object_name) IS NOT NULL ) DROP SYNONYM ' + Table_Name + ' CREATE SYNONYM ' + Table_Name + ' FOR ' + @Database + '.' + @Schema + '.' + Table_Name FROM #Tables WHERE TableID = @ID` – kuklei Oct 22 '18 at 16:30
  • @kuklei It seems like you’d want to drop the synonym in any case, not just if the base_object_name existed? – ErikE Oct 22 '18 at 17:03
3

Is the other database on a separate server (or instance), or does the instance hold both databases?

If this is a single instance, and both databases are on it - use three name notation:

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

If the other database exists on a separate SQL Server instance (you can have more than one SQL Server on a box), or the database exists on a SQL Server instance on a different box/VM - create a Linked Server instance. Then you use four name notation:

SELECT *
  FROM database1.dbo.table_X
  JOIN linked_server_name.database2.dbo.table_Y
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502