One SQL Server Catalog View to Rule Them All

As an enterprise database administrator (DBA), I find myself wanting to look up file information on a regular basis for multiple databases at a time. I may need this information when ascertaining which files are consuming the most space on a particular volume or in looking for databases where files are set to auto-grow by a percentage or some unfortunately small increment. It's that particular example I'll provide below.

sys.master_files is what is known as a System Catalog View—it's grown out of the former master database tables that used to exist prior to SQL 2005 when DBAs could still get to the physical tables in the master database and even make changes to them! Now, it's an object sourced from the resourceDB—the protected brains behind your SQL Server instance and exposed through the master database as a view.

Columns Available in Each Database

The metadata exposed from sys.master_files encompasses columns available in sys.database_files in each of the individual databases. When I'm tapping into file metadata for more than a single database, this is the first place I turn. I have a template for this base query in SQL Server Management Studio (SSMS) and a Snippet associated with it in Redgate's SQLPrompt.

Using sys.master_files is simple. The dreaded SELECT * would be enough to suffice, but I find a bit of T(LC)/SQL (tender-loving-care/SQL) goes a long way to make the information manageable, readable, and valuable:

SELECT DB_NAME(database_id) AS database_name, database_id, [file_id]    , type_desc    , data_space_id    , name AS logical_file_name    , physical_name, (size*8/1024) AS size_mb, CASE max_sizeWHEN -1 THEN 'unlimited'ELSE CAST((cast (max_size AS bigint)) * 8 / 1024 AS VARCHAR(10))END AS max_size_mb    , CASE is_percent_growthWHEN 1 THEN CAST(growth AS VARCHAR(3)) + ' %'WHEN 0 THEN CAST(growth*8/1024 AS VARCHAR(10)) + ' mb'END AS growth_increment    , is_percent_growthFROM sys.master_filesORDER BY 1, type_desc DESC, [file_id];

Figure 1

Figure 2

This base query provides a wealth of information:

Filter Results

You can then proceed to filter results for specific database names, or in the case of identifying auto-growth based upon percentages, by interjecting the following predicate in the WHERE clause:

WHERE is_percent_growth = 1

upon which you could then use the results for identifying files with auto_growth settings based upon a percentage rather than the "Best-Practice" of using a static mb size of appopriate girth. If you can write a simple SQL query, you can avoid the need to use undocumented system stored procedures like sys.sp_MSforeachdb to reach this information cleanly—and with much less overhead and coding than is necessary with that undocumented option.

Related: Using the all_columns System View in SQL Server