Saturday, March 28, 2020

How to get all database-level information using T-SQL

 

How to Retrieve Comprehensive Database Information Using System Views in SQL Server

When managing SQL Server instances, it’s often necessary to gather detailed information about each database—such as file sizes, recovery models, access settings, and last backup dates. This article demonstrates how to retrieve this information using system catalog views like sys.databases and sys.master_files.

What Information Can You Extract?

The query provided below will return a wide range of useful metadata for each database, including:

  • Database ID & Name
  • State (e.g., Online, Restoring, Recovering)
  • Number and Size of Data and Log Files
  • User Access Mode (Single-user, Multi-user)
  • Recovery Model (Simple, Full, Bulk Logged)
  • Compatibility Level (SQL Server version support)
  • Creation Date
  • Last Backup Status and Time
  • Various Options: Full-Text Enabled, Auto-Close, Auto-Shrink, Page Verify, Read-Only, etc.

SQL Script to Retrieve Database Metadata

SELECT
    database_id,
    CONVERT(VARCHAR(25), DB.name) AS dbName,
    state_desc,
    
    -- Count and Size of Data Files
    (SELECT COUNT(*) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'ROWS') AS DataFiles,
    (SELECT SUM((size * 8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'ROWS') AS [Data MB],

    -- Count and Size of Log Files
    (SELECT COUNT(*) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'LOG') AS LogFiles,
    (SELECT SUM((size * 8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'LOG') AS [Log MB],
    
    user_access_desc AS [User Access],
    recovery_model_desc AS [Recovery Model],

    -- Compatibility Level
    CASE compatibility_level
        WHEN 60 THEN '60 - SQL Server 6.0'
        WHEN 65 THEN '65 - SQL Server 6.5'
        WHEN 70 THEN '70 - SQL Server 7.0'
        WHEN 80 THEN '80 - SQL Server 2000'
        WHEN 90 THEN '90 - SQL Server 2005'
        WHEN 100 THEN '100 - SQL Server 2008 & R2'
        WHEN 110 THEN '110 - SQL Server 2012'
        WHEN 120 THEN '120 - SQL Server 2014'
        WHEN 130 THEN '130 - SQL Server 2016'
        WHEN 140 THEN '140 - SQL Server 2017'
        WHEN 150 THEN '150 - SQL Server 2019'
    END AS [Compatibility Level],

    -- Creation Date
    CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation Date],

    -- Last Backup Information
    ISNULL((
        SELECT TOP 1 
            CASE TYPE
                WHEN 'D' THEN 'Full'
                WHEN 'I' THEN 'Differential'
                WHEN 'L' THEN 'Transaction Log'
            END + ' – ' + 
            LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(), backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' + 
            CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' + 
            CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) + ' (' + 
            CAST(DATEDIFF(SECOND, backup_start_date, backup_finish_date) AS VARCHAR(4)) + ' seconds)'
        FROM msdb..backupset BK
        WHERE BK.database_name = DB.name
        ORDER BY backup_set_id DESC
    ), '-') AS [Last Backup],

    -- Optional Features and Settings
    CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [Fulltext],
    CASE WHEN is_auto_close_on = 1 THEN 'Auto-close enabled' ELSE '' END AS [AutoClose],
    page_verify_option_desc AS [Page Verify Option],
    CASE WHEN is_read_only = 1 THEN 'Read-only' ELSE '' END AS [Read Only],
    CASE WHEN is_auto_shrink_on = 1 THEN 'Auto-shrink enabled' ELSE '' END AS [AutoShrink],
    CASE WHEN is_auto_create_stats_on = 1 THEN 'Auto Create Statistics' ELSE '' END AS [Auto Create Stats],
    CASE WHEN is_auto_update_stats_on = 1 THEN 'Auto Update Statistics' ELSE '' END AS [Auto Update Stats],
    CASE WHEN is_in_standby = 1 THEN 'Standby Mode' ELSE '' END AS [Standby],
    CASE WHEN is_cleanly_shutdown = 1 THEN 'Cleanly Shutdown' ELSE '' END AS [Cleanly Shutdown]

FROM sys.databases DB
ORDER BY dbName, [Last Backup] DESC, name;

Reference

For more details, refer to the official Microsoft documentation:
📖 sys.databases (Transact-SQL)

Summary

This query is extremely useful for DBAs who want a consolidated snapshot of each database's configuration, storage usage, and maintenance status. By leveraging system views like sys.databases, sys.master_files, and msdb..backupset, you can automate health checks and create custom monitoring reports tailored to your environment.

 

at

Popular Posts