How do you use the DROP SYNONYM statement to delete a synonym?
Posted by LeoRobs
Last Updated: July 30, 2024
In SQL, the DROP SYNONYM statement is used to delete a synonym, which is an alternate name for a database object such as a table or view. To use the DROP SYNONYM statement, you generally follow this syntax:
DROP SYNONYM [schema.]synonym_name;
Parameters:
- schema. (optional): The name of the schema in which the synonym is defined. If not specified, the synonym is assumed to be in the current schema. - synonym_name: The name of the synonym you want to drop.
Example:
Suppose you have a synonym named my_synonym that points to a table called my_table. To delete this synonym, you would execute the following SQL command:
DROP SYNONYM my_synonym;
If my_synonym is in a specific schema, for example, sales, you would specify it like this:
DROP SYNONYM sales.my_synonym;
Important Notes:
- Once the synonym is dropped, it cannot be used again unless it is re-created. - Be cautious when dropping synonyms because other database objects (like stored procedures or views) may depend on them. Dropping a synonym does not affect the underlying object it points to.