DROP SEQUENCE (Transact-SQL)
Updated: October 28, 2015
Applies To: Azure SQL Database, SQL Server 2014, SQL Server 2016 Preview
Removes a sequence object from the current database.
Applies to: SQL Server (SQL Server 2012 through current version), SQL Database V12.
- IF EXISTS
Applies to: SQL Server (SQL Server 2016 Community Technology Preview 3 (CTP 3.0) through current version).
Conditionally drops the sequence only if it already exists.
Is the name of the database in which the sequence object was created.
Is the name of the schema to which the sequence object belongs.
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
The following example removes a sequence object named CountBy1 from the current database.
DROP SEQUENCE CountBy1 ; GO