11 out of 21 rated this helpful - Rate this topic

CREATE SYNONYM (Transact-SQL)

SQL Server 2005

Creates a new synonym.

Topic link icon Transact-SQL Syntax Conventions


CREATE SYNONYM [ schema_name_1. ] synonym_name FOR < object >

< object > :: =
{
    [ server_name.[ database_name ] . [ schema_name_2 ].| database_name . [ schema_name_2 ].| schema_name_2. ] object_name
}

Arguments

schema_name_1: Specifies the schema in which the synonym is created. If schema is not specified, SQL Server 2005 uses the default schema of the current user.

synonym_name: Is the name of the new synonym.

server_name: Is the name of the server on which base object is located.

database_name: Is the name of the database in which the base object is located. If database_name is not specified, the name of the current database is used.

schema_name_2: Is the name of the schema of the base object. If schema_name is not specified the default schema of the current user is used.

object_name: Is the name of the base object that the synonym references.

Remarks

The base object need not exist at synonym create time. SQL Server checks for the existence of the base object at run time.

Synonyms can be created for the following types of 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	Table1 (User-defined)

1 Includes local and global temporary tables

Four-part names for function base objects are not supported.

Synonyms can be created, dropped and referenced in dynamic SQL.

Permissions

To create a synonym in a given schema, a user must have CREATE SYNONYM permission and either own the schema or have ALTER SCHEMA permission.

The CREATE SYNONYM permission is a grantable permission.

Note:
You do not need permission on the base object to successfully compile the CREATE SYNONYM statement, because all permission checking on the base object is deferred until run time.

A. Creating a synonym for a local object.

The following example first creates a synonym for the base object, Product in the AdventureWorks database, and then queries the synonym.

USE tempdb;
GO
-- Create a synonym for the Product table in AdventureWorks.
CREATE SYNONYM MyProduct
FOR AdventureWorks.Production.Product;
GO

-- Query the Product table by using the synonym.
USE tempdb;
GO
SELECT ProductID, Name 
FROM MyProduct
WHERE ProductID < 5;
GO

Here is the result set.

-----------------------
ProductID   Name                      
----------- --------------------------
1           Adjustable Race
2           Bearing Ball
3           BB Ball Bearing
4           Headset Ball Bearings
(4 row(s) affected)

B. Creating a synonym to remote object

In the following example, the base object, Contact, resides on a remote server named Server_Remote.

EXEC sp_addlinkedserver Server_Remote;
GO
USE tempdb;
GO
CREATE SYNONYM MyEmployee FOR Server_Remote.AdventureWorks.HumanResources.Employee;
GO

Examples

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD

CREATE SYNONYM (Transact-SQL)

Reference

Other Resources

Help and Information

Community Additions