SQL Server Performance Forum – Threads Archive

Dynamically create Synonym

Can i create synonyms dynamically using stored procedures?
I want to give the ServerName, DatabaseName and SchemaName as a parameter to this Stored Procedure.
I need this because there are many databases which i need to be compared and at runtime i need to switch between these databases. the base table is the same but the table which i compare with this will be different. eg. CREATE PROCEDURE sp_Test
@SvrName varchar(Max),
@DBName varchar(Max),
@SchemaName varchar(Max)
AS drop synonym TESTSYN
IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE name = N’TESTSYN’)
CREATE SYNONYM [dbo].[TESTSYN] FOR @[email protected]@SchemaName.[TableName] GO Here @SvrName, @DBName, @SchemaName is a parameter to this Stored Procedure. the Table Name is a table which is present in the other database (Hard Coded).

---

I don’t think you can create synonyms like this but you shoud be able to create synonyms using dynamic sql… Try using dyanamic sql…
MohammedU.
Moderator
SQL-Server-Performance.com

---

Is there any way the creation of a synonym can accept parameters?

---

Try dynamic sql inside the procedure… http://www.sommarskog.se/dynamic_sql.html MohammedU.
Moderator
SQL-Server-Performance.com

---

]]>