Get the size of all databases

...without using dynamic SQL or sp_msForEachDB

A common task for DBA's is to manage the size of all the databases on one or more instances. This often requires the ability to collect DB sizes using T-SQL; most methods I have found involve Dynamic SQL or sp_msForEachDB. These methods work but, in each case, leaves you vulnerable to SQL Injection.

SQL Server 2005 introduced the sys.master_files catalog view. sys.master_files contains everything you need for this (log and data file sizes) except for the database name. That is okay, because it does provide the database_id which can be used for joining to sys.databases which contains the database_id and database name.

This query will get you all db sizes without any Dynamic SQL:

Get All Database Sizes:

;WITH dbs([dbid],size) AS ( SELECT database_id, SUM(size)/128 FROM sys.master_files GROUP BY database_id ) SELECT d2.name db, d1.size [size(mb)] FROM dbs d1 JOIN sys.databases d2 ON d1.dbid=d2.database_id