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:

DBStats: A Stored Procedure for Easily Retrieving Basic DB Information - SQLNetHub
The following catalogs are used:

DDL T-SQL Code for Creating DBStats Stored Procedure

To create the stored procedure, you can use the below T-SQL script:
--
--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!

 

Featured Online Courses:

 

Read Also:

 

Featured Database Security and Administration Tool: DBA Security Advisor

DBA Security Advisor: Secure your SQL Server instances by scanning multiple instances against a rich set of security checks, and by getting recommendations and remediation steps.

DBA Security Advisor - SQL Server Security and Administration Tool

Learn more

 

 


Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips
(special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More


Featured Online Courses:

 

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub