HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Azure SQL Database Elastic Pools - TSQL to get current Elastic Pool

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
tsqlsqlpoolelasticdatabaseazuregetcurrentpools

Problem

I would like to be able to fetch the name of the elastic pool that the current database belongs to using TSQL but the only DMV I can find with a column containing elastic is sys.elastic_pool_resource_stats
& I don't see any reference to databases there. I also checked sys.databases to see if an extra column got sneaked on there but nothing I could recognise.

Ideally this should work like @@servername or db_name()
(when on earth will they get around to creating server_name() or @@dbname so that we can code consistently ?)

Does anyone know a suitable command ?

(btw no tag for "elastic pools")

Solution

What about this?

-- TSQL to find Databases, corresponding elastic pool names and DB edition
 
SELECT
       @@SERVERNAME as [ServerName],
       dso.elastic_pool_name,
       d.name as DatabaseName,
       dso.edition
FROM
       sys.databases d inner join sys.database_service_objectives dso on d.database_id = dso.database_id
WHERE d.Name <> 'master'
ORDER BY
       d.name, dso.elastic_pool_name

Code Snippets

-- TSQL to find Databases, corresponding elastic pool names and DB edition
 
SELECT
       @@SERVERNAME as [ServerName],
       dso.elastic_pool_name,
       d.name as DatabaseName,
       dso.edition
FROM
       sys.databases d inner join sys.database_service_objectives dso on d.database_id = dso.database_id
WHERE d.Name <> 'master'
ORDER BY
       d.name, dso.elastic_pool_name

Context

StackExchange Database Administrators Q#290099, answer score: 5

Revisions (0)

No revisions yet.