Getting Information About Synonyms
The sys.synonyms catalog view contains an entry for each synonym in a given database. This catalog view exposes synonym metadata such as the name of the synonym and the name of the base object. For more information about the sys.synonyms catalog view, see sys.synonyms (Transact-SQL).
By using extended properties, you can add descriptive or instructional text, input masks, and formatting rules as properties of a synonym. Because the property is stored in the database, all applications that read the property can evaluate the object in the same way. For more information, see Using Extended Properties on Database Objects
To find the base type of the base object of a synonym, use the OBJECTPROPERTYEX function. For more information, see OBJECTPROPERTYEX (Transact-SQL).
The following example returns the base type of a synonym's base object that is a local object.
USE tempdb; GO CREATE SYNONYM MyEmployee FOR AdventureWorks2008R2.HumanResources.Employee; GO SELECT OBJECTPROPERTYEX(OBJECT_ID('MyEmployee'), 'BaseType') AS BaseType;
The following example returns the base type of a synonym's base object that is a remote object located on a server named Server1.
EXECUTE sp_addlinkedserver Server1; GO CREATE SYNONYM MyRemoteEmployee FOR Server1.AdventureWorks2008R2.HumanResources.Employee; GO SELECT OBJECTPROPERTYEX(OBJECT_ID('MyRemoteEmployee'), 'BaseType') AS BaseType; GO