Where are Sequences?–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I created a few sequence objects lately to test some things. Since I tended to create and use these, I knew the names. Coming back a few days later, with new queries, I wondered where these were stored. This post shows a few things I learned.

SSMS

Where would you think Sequences are stored. They are a weird object, with the NEXT VALUE FOR syntax being used. This feels DDL-ish. However, if we look at the Object Explorer, there aren’t a lot of choices here.

2020-05-12 10_36_03-SQLQuery7.sql - ARISTOTLE_SQL2017.way0utwest (ARISTOTLE_Steve (56))_ - Microsoft

The area that seems most promising is Programmability, and when I check here, I see them.

2020-05-12 10_37_00-SQLQuery7.sql - ARISTOTLE_SQL2017.way0utwest (ARISTOTLE_Steve (56))_ - Microsoft

If I look at the properties, I can see the settings, which is good. I especially like seeing the current value.

2020-05-12 10_37_20-Sequence Properties - MyKey

 

T-SQL

Since this is an object, it ought to be query-able from T-SQL. sys.objects is a good place to start, and when I query, I see this:

Sequence object results from sys.objects

Good, but not a lot of information here. With SQLPrompt, I do see there is another choice, sys.sequences.

SQL Prompt Intellisense with sys.sequences

If I query this DMV, I now see the same information from sys.objects at the top of the result set.

First few columns of sys.sequences

If I scroll, I can see more of the metadata I am looking for. Last_used_value is important, though I wonder what caching does here. A post for another day.

metadata for the sequence from the DMV

ADS

Azure Data Studio has an Object Explorer, but it’s not as useful, as you can see. No options to get data. In ADS, I’d query the DMV.

Azure Data Studio Object Explorer with just a Refresh option

SQLNewBlogger

This was a quick post, as I tried to find information. Once I did, which took just a minute, I decided to spend 5-10 minutes assembling this post and trying the various tools to get information.

A quick look at how I find the information. You could focus a small post in this way on some learning effort you make.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.