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 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.

The following statement drops the synonym nj_cust, that user cathyg owns:

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.