CREATE SYNONYM (Transact-SQL)
Creates a new synonym.
.gif "Ikona łącza do tematu")
Transact-SQL Syntax Conventions
Składnia
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 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.
Uwagi
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.
[!UWAGA]
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. Creating 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.
USE tempdb;
GO
-- 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.
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.AdventureWorks2012.HumanResources.Employee;
GO
C. Creating a synonym for a user-defined function
The following example creates a function named dbo.OrderDozen that increases order amounts to an even dozen 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