SQL Server 2012 came up with multiple good features. One of them is sequence. I have discussed this feature in my earlier artcile.
In this article, we will discuss how to find list of all sequences along with its current value & other different properties in the database. So, we can achieve this with the help of system view namely sys.sequences.
Given below are the list of properties, we need to find in all sequences in the database.
- Sequence Name
- object ID
- Creation Date
- Last Modified Date
- Schema
- Numeric Precision
- Numeric Scale
- Start Value
- Increment Valye
- Min Value
- Max Value
- Is Cycle Enabled
- Sequence Cache Type
- Cache Size
- Current Value
SELECT seq.name AS [Sequence Name], seq.object_id AS [Object ID], seq.create_date AS [Creation Date], seq.modify_date AS [Last Modified Date], SCHEMA_NAME(seq.schema_id) AS [Schema], CAST(seq.precision AS int) AS [Numeric Precision], CAST(seq.scale AS int) AS [Numeric Scale], ISNULL(seq.start_value,N'''') AS [Start Value], ISNULL(seq.increment,N'''') AS [Increment Value], ISNULL(seq.minimum_value,N'''') AS [Min Value], ISNULL(seq.maximum_value,N'''') AS [Max Value], CAST(seq.is_cycling AS bit) AS [Is Cycle Enabled], ISNULL(seq.cache_size,0) AS [Cache Size], ISNULL(seq.current_value,N'''') AS [Current Value] FROM sys.sequences AS seq
SQL Server Portal 