DROP SYNONYM statement
Use the DROP SYNONYM statement to unregister an existing synonym.
This statement is an extension to the ANSI/ISO standard for SQL.
Syntax
| Element | Description | Restrictions | Syntax |
|---|---|---|---|
| owner | Owner of synonym | Must own synonym | Owner name |
| synonym | Synonym to be dropped | The synonym must exist in the current database. | Identifier |
Usage
This removes the entries for the synonym from the systables, syssynonyms, and syssyntable system catalog tables. You must own the synonym or have the DBA privilege to execute the DROP SYNONYM statement. Dropping a synonym has no effect on the table, view, or sequence object to which the synonym points.
If you include the optional IF EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if no synonym of the specified name is registered in the current database.
DROP SYNONYM cathyg.nj_cust;
DROP SYNONYM is not the only DDL operation that can unregister a synonym. If a table, view, or sequence is dropped, any synonyms that exist in the same database and that refer to that table, view, or sequence object are also dropped.
If a synonym in the current database refers to a dropped table or view in another database, however, that synonym remains registered in the system catalog until you explicitly drop it by using the DROP SYNONYM statement. You can create in the same database another table or view, and declare as its identifier the name of the dropped table or view. (If that is not the name of any table object in the current database, you can instead create a table, view, or sequence object in the current database, and declare as its name the identifier of the table or view that was dropped in the other database.) In either case, the old synonym now refers to the new table object. For a more complete discussion of synonym chaining, see the topic Chaining Synonyms in the CREATE SYNONYM statement description.