While it is simple enough to SELECT DATABASEPROPERTYEX(@DatabaseName, 'MaxSizeInBytes') when connected to a user database, I have been unable to find how to get this information for all of the databases when connected to master.

I want to complete this query:

SELECT  Name                =   d.name
        ,Edition            =   sdo.edition
        ,ServiceObjective   =   sdo.service_objective
        ,CurrentMaxGB       =   ds.?
  FROM  sys.databases d
        JOIN sys.database_service_objectives sdo ON d.database_id = sdo.database_id
        JOIN ? ds                                ON d.database_id = ds.database_id
 WHERE  d.name != N'master'
 ORDER  BY d.name;

However, I have not been able to find a DMV to use for "ds". Can you help?

  • does this help ? docs.microsoft.com/en-us/azure/sql-database/… – Kin Shah Oct 15 '19 at 20:01
  • @KinShah Sadly no, sys.database_files only works within one database. It doesn't even have a column for database_id or database_name. It doesn't come up with the correct number for MAXSIZE anyway, I think it calculates the maximum allocated space. – Mark Freeman Oct 16 '19 at 12:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.