Script per generar un informe de les bases de dades a SQL Server

Aquest script genera un informe amb la informació de les bases de dades a SQL Server:

SELECT
 sys.databases.name as 'Nom base de dades',
 sys.databases.user_access_desc as "Tipus d'accés",
 sys.databases.state_desc as 'Estat',
 sys.databases.is_read_only as 'Només lectura',
 sys.databases.is_auto_shrink_on 'Auto Shrink Activat',
 sys.databases.is_encrypted as 'Encriptada',
 bckup.last_backup as 'Darrer Backup de la Base',
 files.data_file as 'Ruta del fitxer de la base',
 files.log_file as 'Ruta del fitxer de logs',
 files.db_size as 'Mida del fitxer de la base MB',
 files.log_size as 'Mida del fitxer del log MB',
 sys.databases.database_id as 'ID de la base'
FROM sys.databases
 
LEFT JOIN (
 SELECT msdb.dbo.backupset.database_name, MAX(msdb.dbo.backupset.backup_finish_date) AS last_backup
 FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
 WHERE msdb..backupset.type = 'D'

GROUP BY msdb.dbo.backupset.database_name
 ) 
bckup on bckup.database_name = sys.databases.name
 
LEFT JOIN (
 SELECT
 mdf.database_id,
 mdf.name,
 mdf.physical_name as data_file,
 ldf.physical_name as log_file,
 db_size = CAST((mdf.size * 8.0)/1024 AS DECIMAL(8,2)),
 log_size = CAST((ldf.size * 8.0 / 1024) AS DECIMAL(8,2))
 FROM (SELECT * FROM sys.master_files WHERE type_desc
 = 'ROWS' ) mdf
 JOIN (SELECT * FROM sys.master_files WHERE type_desc
 = 'LOG' ) ldf
 ON mdf.database_id = ldf.database_id
 ) files
 
ON files.database_id = sys.databases.database_id
ORDER BY 'Nom base de dades' 

El resultat és semblant a això: