Get database and database file properties

Standard

For a project I had to get the database and database file properties

I came along a lot of scripts on the internet but all had a part of what I wanted so I came up with this script.

The outcome would be something like this:

get database properties

SELECT     d.NAME                AS ‘database name’ , 
           d.compatibility_level AS ‘compatibility level’ , 
           d.collation_name      AS ‘collation’ , 
           d.state_desc          AS ‘state’ , 
           f.physical_name       AS ‘file location’ , 
           d.recovery_model_desc AS ‘recovery model’ , 
           ‘file type’ = ( 
           CASE f.type_desc 
                      WHEN ‘rows’ THEN ‘data’ 
                      ELSE type_desc 
           END ) , 
           ‘size mb’ = (size/128) , 
           ‘max size mb’ = ( 
           CASE CONVERT(                   varchar(20), f.max_size) 
                      WHEN -1 THEN CONVERT(varchar(20), ‘unlimited’) 
                      ELSE CONVERT(        varchar(20), (f.max_size/128)) 
           END ) , 
           ‘growth’ = ( 
           CASE CONVERT(                  varchar(20), f.is_percent_growth) 
                      WHEN 1 THEN CONVERT(varchar(20), f.growth) + ‘ percent’ 
                      ELSE CONVERT(       varchar(20), ((f.growth * 8) / 1024)) + ‘ mb’ 
           END ) 
FROM       sys.databases d 
INNER JOIN sys.master_files f 
ON         d.database_id = f.database_id 
WHERE      d.NAME NOT IN (‘master’, 
                          ’model’, 
                          ’msdb’, 
                          ’tempdb’)

 

Leave a Reply

Your email address will not be published.