patternMinor
Azure SQL Database Elastic Pools - TSQL to get current Elastic Pool
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")
& 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_nameCode 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_nameContext
StackExchange Database Administrators Q#290099, answer score: 5
Revisions (0)
No revisions yet.