DBStats: A Stored Procedure for Easily Retrieving Basic DB Information

In this article, I am publishing a stored procedure (DBStats) I have recently written and which given a database name as an input parameter it returns the below basic information:

• TotalTables
• TotalViews
• TotalSPs
• TotalFunctions
• TotalDMLTriggers
• TotalDBSize (MB)
• LogSize (MB)
• List of all tables with their number of columns

 

Sample Output of DBStats

The below is a screenshot of the output when I execute the stored procedure:

--
--Title: DBStats
--Description: Provides basic statistics for given database
--Supported SQL Server Versions: 2005 or later
--Author: Artemakis Artemiou (Former Data Platform MVP) 
--Date: Sep 9, 2015
--
CREATE PROCEDURE DBStats @databaseName NVARCHAR(60)
AS 
    SET NOCOUNT ON
    SET ANSI_WARNINGS OFF

    --Check if database exists
    --if not, end procedure
    DECLARE @databaseID INT
    DECLARE @DBName VARCHAR(60)
    SET @databaseID=(SELECT COUNT(*) FROM sys.databases WHERE UPPER([name])=UPPER(@databaseName))

    IF @databaseID=0
        BEGIN
            RAISERROR ('Database does not exist. Make sure that you entered the name correctly.',16,1);
            RETURN;
        END;	

    SET @DBName=(SELECT [name] FROM sys.databases WHERE UPPER([name])=UPPER(@databaseName))
     
    CREATE TABLE #tmpDBStats
        (
          value INT ,
          descr VARCHAR(50)
        );

    CREATE TABLE #tmpDBStatsFloat
        (
          value FLOAT ,
          descr VARCHAR(50)
        );

    CREATE TABLE #tmpTableStats
        (
          TableName VARCHAR(60) ,
          TotalColumns INT
        );

--Database Name
    SELECT  @dbName AS DatabaseName;
    PRINT ''

--Total Tables    
    EXEC ('USE ['+@dbName+']; INSERT INTO #tmpDBStats SELECT COUNT(*),''TotalTables''
    FROM   INFORMATION_SCHEMA.TABLES
    WHERE  TABLE_TYPE = ''BASE TABLE''
    AND TABLE_CATALOG = '''+@dbName+''';'
    );

--Total Views    
    EXEC ('USE ['+@dbName+']; INSERT INTO #tmpDBStats SELECT  COUNT(*),''TotalViews''
    FROM    sys.objects o ,
    sys.sql_modules m
    WHERE   o.[object_id] = m.[object_id]
    AND o.[type] IN ( ''V'' );'
    );

--Total SPs    
    EXEC ('USE ['+@dbName+']; INSERT INTO #tmpDBStats SELECT COUNT(*),''TotalSPs''
    FROM      sys.objects o ,
    sys.sql_modules m
    WHERE     o.[object_id] = m.[object_id]
    AND o.[type] IN ( ''P'', ''PC'' );
    ');

--Total Functions    
    EXEC ('USE ['+@dbName+']; INSERT INTO #tmpDBStats SELECT  COUNT(*),''TotalFunctions''
    FROM    sys.objects o ,
    sys.sql_modules m
    WHERE   o.[object_id] = m.[object_id]
    AND o.[type] IN ( ''FN'', ''FS'', ''FT'', ''IF'');');

--Total DML Triggers    
    EXEC ('USE ['+@dbName+']; INSERT INTO #tmpDBStats SELECT    COUNT(*),''TotalDMLTriggers''
    FROM      sys.objects o ,
    sys.sql_modules m
    WHERE     o.[object_id] = m.[object_id]
    AND o.[type] IN ( ''TA'', ''TR'' )');

--DB Size    
    EXEC ('USE ['+@dbName+']; INSERT INTO #tmpDBStatsFloat SELECT total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)),''TotalDBSize (MB)''
    FROM   sys.master_files WITH ( NOWAIT )
    WHERE  database_id = DB_ID()
    GROUP BY database_id');

--Log Size    
    EXEC ('USE ['+@dbName+']; INSERT INTO #tmpDBStatsFloat SELECT log_size_mb = CAST(SUM(CASE WHEN type_desc = ''LOG''
    THEN size END) * 8. / 1024 AS DECIMAL(8,2)),''LogSize (MB)''
    FROM   sys.master_files WITH ( NOWAIT )
    WHERE  database_id = DB_ID() 
    GROUP BY database_id');

--Total Columns
    EXEC ('USE ['+@dbName+']; INSERT INTO #tmpTableStats SELECT  TABLE_NAME AS TableName ,
    COUNT(*) AS TotalColumns
    FROM    INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE'')
    GROUP BY TABLE_NAME
    ORDER BY 2 DESC;');
-----------------------------------

--Display Results
    SELECT  descr AS Statistic,
            value AS Value
    FROM    #tmpDBStats
    UNION	ALL
        SELECT  descr AS Statistic,
            value AS Value
    FROM    #tmpDBStatsFloat;

    PRINT ''

    SELECT  *
    FROM    #tmpTableStats
    ORDER BY TableName;

    PRINT ''

SET NOCOUNT OFF

 

Feel free to post any comments for adding more functionality to the stored procedure!