DROP SYNONYM (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Removes a synonym from a specified schema.

Topic link icon Transact-SQL Syntax Conventions

Syntax


DROP SYNONYM [ IF EXISTS ] [ schema. ] synonym_name  

Arguments

IF EXISTS
||
|-|
|Applies to: SQL Server ( SQL Server 2016 through current version).|

Conditionally drops the synonym only if it already exists.

schema
Specifies the schema in which the synonym exists. If schema is not specified, SQL Server uses the default schema of the current user.

synonym_name
Is the name of the synonym to be dropped.

Remarks

References to synonyms are not schema-bound; therefore, you can drop a synonym at any time. References to dropped synonyms will be found only at run time.

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

Permissions

To drop a synonym, a user must satisfy at least one of the following conditions. The user must be:

  • The current owner of a synonym.

  • A grantee holding CONTROL on a synonym.

  • A grantee holding ALTER SCHEMA permission on the containing schema.

Examples

The following example first creates a synonym, MyProduct, and then drops the synonym.

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

See Also

CREATE SYNONYM (Transact-SQL)
EVENTDATA (Transact-SQL)