View Size of Each Database in SQL Server 2012
In this article I describe how to View Size of Each Database stored in SQL Server.
Introduction
In this article I describe how to show space used by each database stored in SQL Server. Sometimes we need to find particular database size or we need to see the list of size of each database stored in SQL Server. To show all the databases size list, there are following ways to list all the databases.
View size of particular database using SQL Server Management Studio.
- Open Microsoft SQL Server Management Studio.
- Connect to database engine. Open object explorer.
- Click Databases node and expand it. Now see the list of all databases.
- Right click on database whose size is to be viewed and select properties.
Output:
View size of each database on an instance of SQL Server using T-SQL.
SELECT sysdb.name,
CONVERT(varchar,ROUND(SUM(mfile.size) * 8 / 1024, 0)) + 'MB' as Size_MBs
FROM sys.master_files mfile
INNER JOIN sys.databases sysdb ON sysdb.database_id = mfile.database_id
WHERE sysdb.database_id > 4
GROUP BY sysdb.name
ORDER BY sysdb.name
Output:
View size of each database on an instance of SQL Server using Sp_msforeachdb Syastem Stored Procedure:
In my previous article I described how to use Sp_msforeachdb. You can visit from here- sp_MSforeachdb Stored Procedure in SQL Server 2012. This stored procedure allows you to iterate through all databases to perform a specific task to run a query against all the databases. It executes SQL query against each database associated with current SQL Server instance. Run following code to view list of all databases:
declare @cmd varchar(500)
set @cmd='use [?];exec sp_spaceused '
exec sp_MSforeachdb @cmd
Output:
