DROP SEQUENCE statement

Use the DROP SEQUENCE statement to remove a sequence object from the database.

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax


Element	Description	Restrictions	Syntax
owner	Name of sequence owner	Must own the sequence object	Owner name
sequence	Name of a sequence	Must exist in the current database	Identifier

Usage

This statement removes the sequence entry from the syssequences system catalog table. To drop a sequence, you must be its owner or have the DBA privilege on the database. In an ANSI-compliant database, you must qualify the name of the sequence with the name of its owner (owner.sequence) if you are not the owner.

If you include the optional IF EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if no sequence object of the specified name is registered in the current database.

If you drop a sequence, any synonyms for the name of the sequence are also dropped automatically by the database server.

You cannot use a synonym to specify the identifier of the sequence in the DROP SEQUENCE statement.

Examples

Suppose you had a sequence created with something code similar to the following:

CREATE SEQUENCE Invoice_Numbers 
  START 10000 INCREMENT 1 NOCYCLE ;

Such a sequence can be dropped using the following:

DROP SEQUENCE Invoice_Numbers;

Details of existing sequences can be found by joining the syssequences and systables system catalog tables as in the following:

SELECT t.tabname SeqName
  FROM Syssequences s, Systables t
  WHERE t.tabid = s.tabid ;