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:
DDL T-SQL Code for Creating DBStats 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
Featured Online Courses:
- SQL Server 2022: What’s New – New and Enhanced Features
- Working with Python on Windows and SQL Server Databases
- Introduction to Azure Database for MySQL
- Boost SQL Server Database Performance with In-Memory OLTP
- Introduction to Azure SQL Database for Beginners
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- Data Management for Beginners – Main Principles
- A Guide on How to Start and Monetize a Successful Blog
Read Also:
- How to Patch a SQL Server Failover Cluster
- How to Add a Database to a SQL Server Availability Group Using T-SQL
- Top 10 SQL Server DBA Daily Tasks List
- The “Public” Database Role in SQL Server
- … all our SQL Server Administration Articles
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.
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!
Featured Online Courses:
- SQL Server 2022: What’s New – New and Enhanced Features
- Working with Python on Windows and SQL Server Databases
- Introduction to Azure Database for MySQL
- Boost SQL Server Database Performance with In-Memory OLTP
- Introduction to Azure SQL Database for Beginners
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- Data Management for Beginners – Main Principles
- A Guide on How to Start and Monetize a Successful Blog
Read Also:
Feel free to check our other relevant articles on SQL Server troubleshooting:
- How to Resolve: Cannot Connect to WMI Provider (SQL Server Configuration Manager)
- Error converting data type varchar to numeric
- Error converting data type varchar to float
- SQLServerAgent could not be started (reason: Unable to connect to server ‘(local)’; SQLServerAgent cannot start)
- ORDER BY items must appear in the select list if SELECT DISTINCT is specified
- There is no SQL Server Failover Cluster Available to Join
- There is insufficient system memory in resource pool ‘internal’ to run this query.
- … all SQL Server troubleshooting articles
Check our online courses!
Check our eBooks!
Subscribe to our YouTube channel!
Subscribe to our newsletter and stay up to date!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
© SQLNetHub
Artemakis Artemiou, a distinguished Senior Database and Software Architect, brings over 20 years of expertise to the IT industry. A Certified Database, Cloud, and AI professional, he earned the Microsoft Data Platform MVP title for nine consecutive years (2009-2018). As the founder of SQLNetHub and GnoelixiAI Hub, Artemakis is dedicated to sharing his knowledge and democratizing education on various fields such as: Databases, Cloud, AI, and Software Development. His commitment to simplicity and knowledge sharing defines his impactful presence in the tech community.