Get SQL Azure database Edition and Service_Objective using TSQL

Question

Does anyone know how to get an Azure database's Edition (Standard, etc.) and Service_Objective (S1, S2, etc) using TSQL? I can find PowerShell and Portal explanations but I cannot find any reference to TSQL.

Answer 1

You can use this query to check the Service Edition of the Database:

SELECT DATABASEPROPERTYEX('Database_Name', 'EDITION')
SELECT DATABASEPROPERTYEX('Database_Name', 'ServiceObjective')

If the ServiceObjective line is not working, there can be various reasons for that:

1. You might be trying to get service objective for editions that don't have serviceobjective defined. For example, Web Edition. In case of Web Edition, ServiceObjective should return null.

2. As Satya mentioned in their comment, this works only for V12 servers. So if you are trying to run this command on servers prior to V12, this command will not work.

Answer 2

Update I've recently switched to using the Azure system table sys.database_service_objectives. This allows me to also retrieve the SQL Database Elastic Pool name in a single query. Original answer still recorded below for convenience.

SELECT @@VERSION AS AzureVersion,
  db_name() AS [Name],
  edition AS AzureEdition,
  service_objective AS AzureTier,
  elastic_pool_name AS ElasticPool
FROM sys.database_service_objectives

Original Answer

I like to grab all three values at once, using db_name() to point at the current database, and replacing the NULL ServiceObjective with a more useful message for v11 servers.

SELECT @@VERSION AS AzureVersion,
DATABASEPROPERTYEX(DB_NAME(), 'Edition') AS AzureEdition,
COALESCE(DATABASEPROPERTYEX(DB_NAME(), 'ServiceObjective'), 'N/A in v11') AS AzureTier