Skip to content

create-synonym-transact-sql.md

Latest commit

 

History

History
212 lines (162 loc) · 6.76 KB

create-synonym-transact-sql.md

File metadata and controls

212 lines (162 loc) · 6.76 KB
title CREATE SYNONYM (Transact-SQL)
description CREATE SYNONYM (Transact-SQL)
author markingmyname
ms.author maghan
ms.date 02/01/2022
ms.prod sql
ms.prod_service database-engine, sql-database
ms.technology t-sql
ms.topic reference
f1_keywords
CREATE_SYNONYM_TSQL
SYNONYM_TSQL
SYNONYM
CREATE SYNONYM
helpviewer_keywords
alternate names [SQL Server]
names [SQL Server], synonyms
CREATE SYNONYM statement
synonyms [SQL Server], creating
dev_langs
TSQL

CREATE SYNONYM (Transact-SQL)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]

Creates a new synonym.

Topic link icon Transact-SQL Syntax Conventions

Syntax

-- SQL Server Syntax  
  
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  
}  

-- Azure SQL Database Syntax  
  
CREATE SYNONYM [ schema_name_1. ] synonym_name FOR < object >  
  
< object > :: =  
{  
    [database_name. [ schema_name_2 ].| schema_name_2. ] object_name  
}

[!INCLUDEsql-server-tsql-previous-offline-documentation]

Arguments

schema_name_1

Specifies the schema in which the synonym is created. If schema_name isn't specified, [!INCLUDEssNoVersion] uses the default schema of the current user.

synonym_name

Is the name of the new synonym.

server_name

Applies to: [!INCLUDEssKatmai] and later.

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 isn't 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 isn't specified, the default schema of the current user is used.

object_name

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

[!INCLUDEssSDSfull] supports the three-part name format database_name.[schema_name].object_name when the database_name is the current database or the database_name is tempdb and the object_name starts with #.

Remarks

The base object need not exist at synonym create time. [!INCLUDEssNoVersion] 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
  • Table1 (User-defined)
  • View

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.

Note

Synonyms are database-specific and cannot be accessed by other databases.

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.

Examples

A. Create a synonym for a local object

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

-- Create a synonym for the Product table in AdventureWorks2012.  
CREATE SYNONYM MyProduct  
FOR AdventureWorks2012.Production.Product;  
GO  
  
-- Query the Product table by using the synonym.  
SELECT ProductID, Name   
FROM MyProduct  
WHERE ProductID < 5;  
GO

[!INCLUDEssResult]

----------------------- 
ProductID   Name 
----------- -------------------------- 
1           Adjustable Race 
2           Bearing Ball 
3           BB Ball Bearing 
4           Headset Ball Bearings 

(4 row(s) affected)

B. Create a synonym to remote object

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

Applies to: [!INCLUDEssKatmai] and later.

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

C. Create a synonym for a user-defined function

The following example creates a function named dbo.OrderDozen that increases order amounts to 12 units. The example then creates the synonym dbo.CorrectOrder for the dbo.OrderDozen function.

-- Creating the dbo.OrderDozen function  
CREATE FUNCTION dbo.OrderDozen (@OrderAmt INT)  
RETURNS INT  
WITH EXECUTE AS CALLER  
AS  
BEGIN  
IF @OrderAmt % 12 <> 0  
BEGIN  
    SET @OrderAmt +=  12 - (@OrderAmt % 12)  
END  
RETURN(@OrderAmt);  
END;  
GO  
  
-- Using the dbo.OrderDozen function  
DECLARE @Amt INT;  
SET @Amt = 15;  
SELECT @Amt AS OriginalOrder, dbo.OrderDozen(@Amt) AS ModifiedOrder;  
  
-- Create a synonym dbo.CorrectOrder for the dbo.OrderDozen function.  
CREATE SYNONYM dbo.CorrectOrder  
FOR dbo.OrderDozen;  
GO  
  
-- Using the dbo.CorrectOrder synonym.  
DECLARE @Amt INT;  
SET @Amt = 15;  
SELECT @Amt AS OriginalOrder, dbo.CorrectOrder(@Amt) AS ModifiedOrder;

See also

Next steps