CREATE SYNONYM (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Creates a new synonym.

Transact-SQL syntax conventions

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
}

Specifies the schema in which the synonym is created. If schema_name isn't specified, SQL Server uses the default schema of the current user.

The name of the new synonym.

The name of the server on which base object is located.

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.

The name of the schema of the base object. If schema_name isn't specified, the default schema of the current user is used.

The name of the base object that the synonym references.

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 (CLR) aggregate functions
  • Replication-filter-procedure
  • Extended stored procedure
  • T-SQL scalar function
  • T-SQL table-valued function
  • T-SQL inline-table-valued function
  • T-SQL stored procedure
  • Table (user-defined, includes local and global temporary tables)
  • View

• Four-part names for function base objects aren't supported.

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

• Synonyms are database-specific, and can't be accessed by other databases.

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.

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

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

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

Here's the result set.

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

(4 row(s) affected)

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.AdventureWorks2022.HumanResources.Employee;
GO

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;

• DROP SYNONYM (Transact-SQL)
• EVENTDATA (Transact-SQL)
• GRANT (Transact-SQL)
• Synonyms (Database Engine)

• Create Synonyms