Skip to content

Instantly share code, notes, and snippets.

@matthew-n
Last active January 21, 2021 15:22
Show Gist options
  • Save matthew-n/07172c375884e2615483ae5b586992b5 to your computer and use it in GitHub Desktop.
Save matthew-n/07172c375884e2615483ae5b586992b5 to your computer and use it in GitHub Desktop.
dmv based alternative to sp_helpdb
/* 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;
@matthew-n
Copy link
Author

added bracket quotes

@matthew-n
Copy link
Author

simplify the conditionals

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment