3

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")

2 Answers 2

5

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
1
0

Found it, though the solution is rather limited, because this DMV is only available in the master database and you can't actually refer to it from within a user database.

select d.name,  o.* 
from sys.database_service_objectives o
join sys.databases d on
  o.database_id = d.database_id
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.