A synonym is a database object that serves the following purposes:
- Provides an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.
- Provides a layer of abstraction that protects a client application from changes made to the name or location of the base object.
For example, consider the Employee table of Adventure Works, located on a server named Server1. To reference this table from another server, Server2, a client application would have to use the four-part name Server1.AdventureWorks.Person.Employee. Also, if the location of the table were to change, for example, to another server, the client application would have to be modified to reflect that change.
To address both these issues, you can create a synonym, EmpTable, on Server2 for the Employee table on Server1. Now, the client application only has to use the single-part name, EmpTable, to reference the Employee table. Also, if the location of the Employee table changes, you will have to modify the synonym, EmpTable, to point to the new location of the Employee table. Because there is no ALTER SYNONYM statement, you first have to drop the synonym, EmpTable, and then re-create the synonym with the same name, but point the synonym to the new location of Employee.
A synonym belongs to a schema, and like other objects in a schema, the name of a synonym must be unique.
Example:
IF EXISTS (select * from sysobjects where id = object_id('dbo.snObject01', 'SN') )
BEGIN
DROP SYNONYM dbo.snObject01
IF OBJECT_ID('dbo.snObject01', 'SN') IS NOT NULL
PRINT '<<< FAILED DROPPING SYNONYM dbo.snObject01 >>>'
ELSE
PRINT '<<< DROPPED SYNONYM dbo.snObject01 >>>'
END
GO
IF(@@SERVERNAME = 'PRODDB01')
BEGIN
CREATE SYNONYM dbo.snObject01 FOR PRODDB02.DMS.dbo.MatchTripComplete;
END;
ELSE
BEGIN
CREATE SYNONYM dbo.snObject01 FOR DMS.dbo.MatchTripComplete;
END;
GO
IF OBJECT_ID('dbo.snObject01', 'SN') IS NOT NULL
PRINT '<<< CREATED SYNONYM dbo.snObject01 >>>';
ELSE
PRINT '<<< FAILED SYNONYM PROCEDURE dbo.snObject01 >>>';
GO
GRANT SELECT ON dbo.snObject01 TO MyRole01;
GO
DROP SYNONYM [dbo].[snvObject01]
GO
CREATE SYNONYM [dbo].[snvObject01] FOR [SERVER01].[DB01].[dbo].[vMyView01]
GO
Comments