Every database with its state, size, recovery model, what is holding up log reuse, and settings worth knowing (auto close, auto shrink, page verify, Query Store, RCSI, encryption). Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025.

-- 02_Databases.sql - every database: state, size, recovery model and settings worth knowing. Read-only.  Clive Roberts DBA
SET NOCOUNT ON;
SELECT d.name, d.state_desc, d.recovery_model_desc, d.compatibility_level, SUSER_SNAME(d.owner_sid) AS owner,
       CONVERT(decimal(12,1), SUM(CASE WHEN mf.type = 0 THEN mf.size END) / 128.0) AS data_mb,
       CONVERT(decimal(12,1), SUM(CASE WHEN mf.type = 1 THEN mf.size END) / 128.0) AS log_mb,
       d.log_reuse_wait_desc, d.page_verify_option_desc, d.is_auto_close_on, d.is_auto_shrink_on,
       d.is_query_store_on, d.is_read_committed_snapshot_on, d.snapshot_isolation_state_desc, d.is_encrypted, d.create_date
FROM sys.databases AS d JOIN sys.master_files AS mf ON mf.database_id = d.database_id
GROUP BY d.name, d.state_desc, d.recovery_model_desc, d.compatibility_level, d.owner_sid, d.log_reuse_wait_desc,
         d.page_verify_option_desc, d.is_auto_close_on, d.is_auto_shrink_on, d.is_query_store_on,
         d.is_read_committed_snapshot_on, d.snapshot_isolation_state_desc, d.is_encrypted, d.create_date
ORDER BY d.name;