DROP SEQUENCE (Transact-SQL)
Applies To: 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).
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