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.
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.
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.
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]
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With