I ran into an interesting problem today. I needed to find out the pricing tier of an Azure SQL DB but while I have access to to the DB via SSMS I don’t have access via the Portal. So I needed something I could use via T-SQL. I did some research, and found something that said it worked but didn’t look right to me. So I asked on twitter.

Note: I love #sqlhelp. Joey D’Antoni (blog|twitter) answered me within a couple of minutes. Unfortunately he referenced the same thing I’d already found. Fortunately after looking at it again I found what I’d been doing wrong. First the DMV.

sys.database_service_objectives

The reason I ran into a problem was that I was looking at an Azure SQL DW, which I’m not really used to. So DW400c didn’t mean anything to me. On the other hand when I ran it on a DB I started getting the results I expected. Now, if you run this while connected to your Azure SQL DB you get just the information for the current DB.

On the other hand if you run it in master you get all of the databases (Or just the ones you have access to, I’m not sure and haven’t tested. Although BOL does say all.) Now, it also uses the database_id so since I’m getting back more than just my current DB I tied in sys.databases

SELECT d.name, dso.*
FROM sys.database_service_objectives dso
JOIN sys.databases d
	ON dso.database_id = d.database_id
ORDER BY d.name;

You’ll notice that the service_objective is the pricing tier while edition is the service tier, with the elastic_pool_name being the .. you guessed it .. name of the elastic pool if there is one.