Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Expand Minimize

DROP SEQUENCE (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Removes a sequence object from the current database.

Applies to: SQL Server (SQL Server 2012 through current version).

Topic link icon Transact-SQL Syntax Conventions

DROP SEQUENCE { [ database_name . [ schema_name ] . | schema_name. ]    sequence_name } [ ,...n ]
 [ ; ]

database_name

Is the name of the database in which the sequence object was created.

schema_name

Is the name of the schema to which the sequence object belongs.

sequence_name

Is the name of the sequence to be dropped. Type is sysname.

After generating a number, a sequence object has no continuing relationship to the number it generated, so the sequence object can be dropped, even though the number generated is still in use.

A sequence object can be dropped while it is referenced by a stored procedure, or trigger, because it is not schema bound. A sequence object cannot be dropped if it is referenced as a default value in a table. The error message will list the object referencing the sequence.

To list all sequence objects in the database, execute the following statement.

SELECT sch.name + '.' + seq.name AS [Sequence schema and name] 
    FROM sys.sequences AS seq
    JOIN sys.schemas AS sch
        ON seq.schema_id = sch.schema_id ;
GO

Permissions

Requires ALTER or CONTROL permission on the schema.

Audit

To audit DROP SEQUENCE, monitor the SCHEMA_OBJECT_CHANGE_GROUP.

The following example removes a sequence object named CountBy1 from the current database.

DROP SEQUENCE CountBy1 ;
GO

Community Additions

ADD
Show:
© 2015 Microsoft