In SQL Server, we can use the sys.sequences
system catalog view to return a list of sequence objects in the current database.
Example
The following example uses the WideWorldImporters
sample database:
SELECT
SCHEMA_NAME(schema_id) AS [Schema],
name AS [Sequence],
current_value,
start_value,
increment,
minimum_value,
maximum_value,
is_cycling,
is_cached,
TYPE_NAME(system_type_id) AS system_type,
TYPE_NAME(user_type_id) AS user_type,
[precision],
scale,
is_exhausted
FROM sys.sequences
ORDER BY name;
Result:
Schema Sequence current_value start_value increment minimum_value maximum_value is_cycling is_cached system_type user_type precision scale is_exhausted ------ --------- ------------- ----------- --------- -------------------- ------------------- ---------- --------- ----------- --------- --------- ----- ------------ dbo Sequence1 235 100 5 -9223372036854775808 9223372036854775807 false true bigint bigint 19 0 false dbo Sequence2 1 1 1 -9223372036854775808 9223372036854775807 true true bigint bigint 19 0 false dbo Sequence3 255 0 1 0 255 false true tinyint tinyint 3 0 true dbo Sequence4 37 1 1 -9223372036854775808 9223372036854775807 false true bigint bigint 19 0 false dbo Sequence5 1 1 1 0 5 true true tinyint tinyint 3 0 false dbo Sequence6 2990 1000 10 -32768 5000 true true smallint smallint 5 0 false 6 row(s) returned
Here, I used the SCHEMA_NAME()
function to get the schema name from the schema_id
value. I also used the TYPE_NAME()
function to get the data type names from their IDs.
The sys.sequences
view returns more columns than are listed here. As with any SELECT
statement, we can the *
wildcard to return all rows.