Listing all sequences in a SQL Server database

Question

I tried:

SELECT * 
FROM sys.sequences

I get:

Error: The "variant" data type is not supported.
SQLState: 08S01
ErrorCode: 0

I know there are sequences. Is there a way to see sequences of a table in SQL Server Management Studio in the GUI somehow? Thanks.

EDIT: I noticed that this doesn't work with SQL Squirrel client program but the very same query can be used succesfully in SQL Server Management Studio.

score 23 · Answer 1 · answered Apr 18 '18 at 14:29

23

If you want to know the sequences and values, you can cast the variant types. For example the following will give most of the details you may be looking for:

SELECT
  name,
  cast(start_value AS NUMERIC)   AS start_value,
  cast(increment AS NUMERIC)     AS increment,
  cast(current_value AS NUMERIC) AS current_value
FROM sys.sequences;

answered Apr 18 '18 at 14:29

NealeU

1,264
11
23

Arithmetic overflow error converting bigint to data type numeric. – Soner from The Ottoman Empire Nov 17 '21 at 06:45
@SonerfromTheOttomanEmpire Do you remember what version that is with? Sorry not to ask 2 years ago! – NealeU May 15 '23 at 14:23
It has nothing to do with its version. It is vulnerable to be overflowed. – Soner from The Ottoman Empire May 15 '23 at 15:45
Okay. That's going to be a bit database to get that. It certainly worked for me when I needed it. – NealeU May 16 '23 at 16:27

score 15 · Answer 2 · answered Aug 30 '17 at 08:13

15

You can find sequences in SSMS in object explorer under Programmability:

answered Aug 30 '17 at 08:13

Andrea

11,801
17
65
72

2

This is of course always an option, if you just want to see them. If you want to use them in apps, you must know how to query them ;) – campovski Aug 30 '17 at 08:21

score 4 · Answer 3 · answered Aug 30 '17 at 08:10

4

What SQL Server are you using? From documentation: In SQL Server 2005 and later versions, the visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. Are you sure you are running the script with proper permissions?

answered Aug 30 '17 at 08:10

campovski

2,979
19
38

I actually I just noticed the following: I used SQL Squirrel at first. The query didn't work in that. Then I tried the exact same query in SQL Management Studio an it worked. I got the list I wanted. It could have something to do how I authorize myself in each client or just in the different ways they access the database engine. – Steve Waters Aug 30 '17 at 08:13
1

Oh, SQL Squirrel. Yep, probably the program didn't have permissions. – campovski Aug 30 '17 at 08:20

Listing all sequences in a SQL Server database

3 Answers3