One of my friend and extremely smart DBA Jonathan from Las Vegas has pointed out nice little enhancement in T-SQL. I was very pleased when I learned about SYNONYM feature in SQL Server 2005.
DBA have been referencing database objects in four part names. SQL Server 2005 introduces the concept of a synonym. A synonyms is a single-part name which can replace multi part name in SQL Statement. Use of synonyms cuts down typing long multi part server name and can replace it with one synonyms. It also provides an abstractions layer which will protect SQL statement using synonyms from changes in underlying objects (tables etc).
Create Synonyms :
USE AdventureWorks;
GO
CREATE SYNONYM MyLocation
FOR AdventureWorks.Production.Location;
GO
Use Synonyms :
USE AdventureWorks;
GO
SELECT TOP 5 *
FROM MyLocation;
GO
Drop Synonyms :
USE AdventureWorks;
GO
DROP SYNONYM MyLocation;
GO
Synonyms can be created on only following objects.
- Assembly (CLR) Stored Procedure
- Assembly (CLR) Table-valued Function
- Assembly (CLR) Scalar Function
- Assembly Aggregate (CLR) Aggregate Functions
- Replication-filter-procedure
- Extended Stored Procedure
- SQL Scalar Function
- SQL Table-valued Function
- SQL Inline-table-valued Function
- SQL Stored Procedure
- View
- Table (User-defined)
Additionally SYNONYMS can be used only to change data of object not the schema of the object. SYNONYMS can be used with only SELECT, UPDATE, INSERT, DELETE, EXECUTE commands.
Following is image demonstrates use of SYNONYMS.
An example of the usefulness of this might be if you had a stored procedure on a Users database that needed to access a Clients table on another production server. Assuming you created the stored procedure in the database Users, you might want to set up a synonym such as the following:
USE Users;
GO
CREATE SYNONYM Clients
FOR Offsite01.Production.dbo.Clients;
GO
Now when writing the stored procedure instead of having to write out that entire alias every time you accessed the table you can just use the alias Clients. Furthermore, if you ever change the location or the name of the production database location all you need to do is modify one synonym instead of having to modify all of the stored procedures which reference the old server.
Reference : Pinal Dave (https://blog.sqlauthority.com), Jonathan (Las Vegas), BOL – SYNONYMS
33 Comments. Leave new
Hi Pinal,
I am working on SQL Server 2005 from last 2 years, mainly on SSRS and Database as Developer.Today I got this site while searching answer for some question, this is great site for study.
Thanks for your effort….!
Vishal.
Hi Pinal,
Is it possible to create a synonyms in Sql server 2005 for a Oracle DB?
Do you know what kind of security authority needs to be applied before a syn can be created?
I really like the SQL Synonyms but not being able to use the SET IDENTITY_INSERT is a real show-stopper. Do you know any way to insert into an identity column using a synonym?
Dear sir,
It is excellent site 4 learning SQL Server.I have got problem
connecting with C# 2008 to SQL Server 2005.plz advice me.
Great Job
dear Pinal,
Really you are doing wonderful job
Keep It Up
best regards
Coimbatore S. Arul Kumar Msc It
Pinal,
I am trying to create a synonym for first 3 part of the object (without object name itself).
For example, I have a linked server named “Legacy”. What I need is a “MyLegacy” synonym or alias for “Legacy.MyDatabase.dbo”, so I can use something like MyLegacy.Table1, MyLegacy.View2 etc.
I don’t want to provide an object name while creating synonyms (otherwise I will end up with huundreds of synonyms, one for each object).
Is there any way to do this?
Thanks.
There is a way of doing this but its not what you would think. I had the same need and this is what I came up with.
Setup 1 synonym for 1 DB object. You wil not be using the synonym at all. What you will be doing is pulling the Server.Database.Schema from the synonym, then use the Server.Database.Schema in String SQL.
Below is the code I am using to extract the Server.Database.Schema from the synonym. You could use the Server.Database.Schema from the synonym to dynamically build a Synonym as needed then drop when done if you do not want to use string sql.
Declare @ServerToUpdt varchar(255)
,@DBToUpdt varchar(255)
,@Schema char(3)
Select @ServerToUpdt = Replace(Replace(Left(base_object_name,charindex(‘.’,base_object_name)-1),'[‘,”),’]’,”)
,@DBToUpdt = Replace(Replace(Substring(base_object_name,
charindex(‘.’,base_object_name)+1,
charindex(‘.’,base_object_name,charindex(‘.’,base_object_name)+1) – charindex(‘.’,base_object_name) – 1)
,'[‘,”),’]’,”)
,@Schema = Replace(Replace(Substring(base_object_name,
charindex(‘.’,base_object_name,charindex(‘.’,base_object_name)+1)+1,
charindex(‘.’,base_object_name,charindex(‘.’,base_object_name,charindex(‘.’,base_object_name)+1)+1) – charindex(‘.’,base_object_name,charindex(‘.’,base_object_name)+1) – 1)
,'[‘,”),’]’,”)
From sys.synonyms Where name = ‘icc_SaveRptParamsCutAudit’
Print @ServerToUpdt
Print @DBToUpdt
Print @Schema
Hi Pinal,
You are doing a great job……,
Biju.K.S
Hi Pinal,
I created synonym for a stored procedure with 3 input parameters.
The synonym works great as long as the Server defined in the synonym is different from the server the synonyn is being executed.
When the synonym is executed on the same server as defined in the synonym I get the following error:
Msg 201, Level 16, State 4, Procedure @Param1, Line 1186
Procedure or function ‘MyProc’ expects parameter ‘@Param1′, which was not supplied.
Can I not use a synonym with a stored procedure object on the same server as defined in the synonym?
Is there a system setting I need to set?
Do I need to clap my hands and spin 3 times?
For example:
Create the synonym on ProdServer.ProdDB
USE ProdDB
CREATE SYNONYM MySynProc
FOR ProdServer.ProdDB.MyProc;
Using the Synonym on a different Server works great
Log onto server ReportServer
USE ProdDB_Rpt
Exec MySynProc @Param1=’A’, @Param2=’1′, @Param3=’Gold’
Using the Synonym on the Server defined in the Synonym generates an error
Log onto server ProdServer
USE ProdDB_Copy
Exec MySynProc @Param1=’A’, @Param2=’1′, @Param3=’Gold’
Using the Synonym on the Server & database defined in the Synonym generates an error
Log onto server ProdServer
USE ProdDB
Exec MySynProc @Param1=’A’, @Param2=’1′, @Param3=’Gold’
I realize this is an old post but since there is no solution posted yet, here it is:
If you are running a stored procedure as a synonym on the same server, you must not specify the server name when creating the synonym.
Thanks Chris, I was also stuck on an issue due to this. Your post helped :)
Hi Pinal,
Sorry, I should have added that in the 3 examples from my previous posting I am executing the synonym from with in a stored procedure.
The Exec MySynProc @Param1=’A’, @Param2=’1′, @Param3=’Gold’ is on line 1186 of the stored procedure.
This stored procedure is used for a report in reporting services. The Synonym is a stored procedure used to update the application audit log table. I am updating the audit log from the report stored procedure because we are also saving the selected report parameter values in the audit log table.
Have been MSSQL DBA for a long time and somehow I missed this feature! :)
HI……..
this is harish i am having 54% in b.tech and i have joined in a company and i doing sql server 2005. i would like to know how i can step into mnc’s with this percentage and my domain………..
hi…….
this is harish. i am from cse department and i having 54% in b.tech and i would like to know how to step up into mnc’s…………. with oracle domain.
How can I delete all synonyms from one database at one time???
Hi
Can it be done on a 2 parter like for Server.DatabaseName instead of a 4 parter?
Hi pinal bai….
your doing geat job…
Thank u
Hi Pinal, Whenever I search for any query or problem SQL related it always pops up yours name first, I find your articles and solutions correct and to the point. Coming to the point, I want to know what happens under the hood when we create a Synonym in SQL. As for Oracle I read it somewhere that it creates a pointer to that table. So whats the scenario in SQL? I have one more query regarding it, if in SQL also it creates a pointer then what happens when we create a Synonym for the table from a database to the linked server in SQL.?
Hi Pinal,
I have requirement where i want to rename the tables which has depdency with many objects, is there any simple solution to rename tables having dependencies wtih that table?
Here is an update to MIKE M. script using the PARSENAME
Select base_object_NAME
, PARSENAME(base_object_name, 4) AS ‘Server’
, PARSENAME(base_object_name, 3) AS ‘Database’
, PARSENAME(base_object_name, 2) AS ‘Schema’
, PARSENAME(base_object_name, 1) AS ‘Object’
From sys.synonyms Where name = ”
Thanks Dan! Simple and direct logic. I appreciate your posting and I will be implementing this change soon.