dmv based alternative to sp_helpdb

better_sp_helpdb.sql

/* original code from: http://davebland.com/a-faster-alternative-to-sp_helpdb */

DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;

SET @t1 = GETDATE();

CREATE TABLE #DBSize
(
    DatabaseName VARCHAR(200),
    Size BIGINT
);

INSERT INTO #DBSize
SELECT d1.name,
       CONVERT(VARCHAR, SUM(m.size) * 8 / 1024) AS [Total disk space]
FROM sys.databases d1
    INNER JOIN sys.master_files m
        ON d1.database_id = m.database_id
GROUP BY d1.name
ORDER BY d1.name;

SELECT 
	CONVERT(VARCHAR(50), d.name) AS [Name],
	s.Size AS [DatabaseSize(MB)],
    d.create_date,
    d.compatibility_level,
    IIF(d.is_auto_create_stats_on = 1, 'True', 'False') AS [AutoStatsOn],
    IIF(d.is_auto_update_stats_on = 1, 'True', 'False') AS [AutoUpdateStatsOn],
    b.name AS [DBOwner],
    CASE d.state
        WHEN 0 THEN 'ONLINE'
        WHEN 1 THEN 'RESTORING'
        WHEN 2 THEN 'RECOVERING'
        WHEN 3 THEN 'RECOVERY_PENDING'
        WHEN 4 THEN 'SUSPECT'
        WHEN 5 THEN 'EMERGENCY'
        WHEN 6 THEN 'OFFLINE'
        WHEN 7 THEN 'COPYING'
        WHEN 10 THEN 'OFFLINE_SECONDARY'
        ELSE 'Unknown State'
    END AS [State],
    SERVERPROPERTY('ProductMajorversion') AS [ProductMajorVersion],
    ISNULL(DB_NAME(d.source_database_id), 'Not A Snapshot') AS [SourceDBName],
    d.create_date,
    d.collation_name,
    d.user_access_desc,
    IIF(d.is_read_only = 1, 'True', 'False') AS [IsReadOnly],
    IIF(d.is_auto_close_on = 1, 'True', 'False') AS [IsAutoCloseOn],
    IIF(d.is_auto_shrink_on = 1, 'True', 'False') AS [IsAutoShrinkOn],
    d.state_desc,
    DATABASEPROPERTYEX(d.name, 'Recovery') AS [RecoveryModel],
    d.log_reuse_wait_desc,
    d.containment_desc, /*This column will need be removed for older versions.*/
    d.delayed_durability_desc,
    IIF(d.is_memory_optimized_enabled =1,'True','False') AS [IsMemoryOptimizedEnabled], /*This column will need to be removed for older versions.*/
    DATABASEPROPERTYEX(d.name, 'Updateability') AS [UpdateAbility],
    DATABASEPROPERTYEX(d.name, 'SQLSortOrder') AS [SQLSortOrder],
    IIF(DATABASEPROPERTYEX(d.name, 'IsFulltextEnabled') = 1, 'True', 'False') AS [IsFulltextEnabled],
    DATABASEPROPERTYEX(d.name, 'Version') AS [Version]
FROM sys.databases d
INNER JOIN sys.syslogins b
    ON d.owner_sid = b.sid
LEFT JOIN #DBSize s
    ON d.name = s.DatabaseName;

DROP TABLE #DBSize;

SET @t2 = GETDATE();
SELECT DATEDIFF(MILLISECOND, @t1, @t2) AS elapsed_ms;

simplify the conditionals