SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



List Database Size using SQL Server sp_MSForEachDB Stored Procedure

SQL Server sp_MSForEachDB stored procedure helps SQL Server database administrators and SQL developers to get a list of SQL Server databases and the database sizes.
To find a SQL Server database size, the system view sysfiles is used.
And to select the database size using sysfiles in every database, the undocumented stored procedure sp_MSForEachDB is used.

SysFiles system view has the database name column, and the database size in integer value, and some other columns.
What is important with sysfiles system view size column is that the number in size column shows the number of 8kb data pages in the database file.
For more detail please check sql tutorial sysfiles.

Let's first create a database table which will be used to store database name and database size values in it.
Please note that the groupid column is used to distinguish database data files from database log files.
If groupid is 1, then it is database data file.
If groupid is 0, then it is a database log file.

Create Table DatabaseSize (
 dbname sysname,
 dbsize bigint,
 groupid smallint
)

Then let's execute the following SQL Server stored procedure sp_MSForEachDB
Please note that, the sp_MSForEachDB stored procedure takes the sql statement as an argument and executes it on each database existing on the current SQL Server instance.

The default "?" question mark has a special meaning in sp_MSForEachDB procedure, it is used as an alias to the database name that the sp_MSForEachDB is executing for.

EXEC sp_MSForEachDB '
 Insert Into DatabaseSize
 SELECT "?", sum(size) * 8, groupid FROM [?]..sysfiles Group By groupid'

select * from DatabaseSize

The above SQL script will first insert all size and database name information from sysfiles into DatabaseSize table.
And then execute a select statement on the DatabaseSize table to list all databases' name and size data.

sp_MSForEachDB for database size

One critical point about the above t-sql script is that if a database has more than one data file, which means beside primary data file or .mdf data file if there are some secondary data files or .ndf data files then an aggregation should be executed.
And within the SQL Server sp_MSForEachDB stored procedure that sql aggregation is executed using "Group By groupid" syntax.

An other SQL solution for database developers to list database sizes on a SQL Server instance is to use system stored procedure sp_databases.

Here is how you can use sp_databases

exec sp_databases

And the output is as follows, showing database name and database size in return list

SQL stored procedure sp_databases to list database size


SQL Tutorials about sp_Msforeachdb and sp_MSForEachTable

sp_MSForEachTable Example T-SQL Code to Count all Rows in all Tables in MS SQL Server Database
sp_Msforeachdb Example : List All Database Files using sp_Msforeachdb Undocumented Stored Procedure
Create Same Stored Procedure on All Databases using sp_MSForEachDB T-SQL Example
MS SQL Server Execute Undocumented Stored Procedures sp_MSForEachDB and sp_MSForEachTable with Example T-SQL Codes
Listing All MS SQL Server Databases Using T-SQL
SQL Server Last Database Access using Last Batch Date of sysprocesses or using SQL Server Audit Files and Database Audit Specifications
Create Database Snapshot for all Databases on SQL Server






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems