Tuesday, August 28, 2012

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:

No comments:

Post a Comment