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.
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 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