SQL Server - Is there a way to query the specific Size and Space available settings for each Database, including system databases on an instance? I am trying to query the two default database properties as seen in the picture below for all databases on an instance, including system databases.
I've used various scripts such as the following, but I'm not sure exactly where data for those two fields are stored.
SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id


Thank you for your help.
Just check the properties of the model database - everything else being equal a new database is created using the model database as a template.
If you want to change the default size, resize your model database - job done.
Note however if the database creation script supplies a specific value that will take precedence.
re-read the question including your title and I think you want the results of running
exec sp_helpdb

or try if you want to see the space available
exec sp_msforeachdb &quot;use [?]; exec sp_spaceused&quot;