Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 655
Haoest Avatar asked Aug 25 '10 19:08

Haoest


People also ask

How do you create a synonym for a table in another database?

Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym. Synonyms provide both data independence and location transparency.

How do I script a SQL database with all tables?

Generate Database Script in SQL Server Now right-click the database then Tasks->Generate scripts. After that a window will open. Select the database and always check "script all objects in the selected database". It will generate a script for all the tables, sp, views, functions and anything in that database.


2 Answers

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]
like image 80
Joe Stefanelli Avatar answered Oct 06 '22 05:10

Joe Stefanelli


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.

like image 30
ErikE Avatar answered Oct 06 '22 05:10

ErikE