Database context for sys.databases and sys.master_files

Question

I have a query that pulls information for a database from the following tables

1. sys.master_files
2. sys.databases
3. sys.filegroups

Currently, the context set is the specific database which I want to query about.
But if the database is in a mode such that USE DB_NAME doesn’t work,
is it okay to use USE master?
Since I am adding a where clause on the basis of DB_NAME will USE DB_NAME versus USE master make any difference?

asked 2021-11-15 by Sonali Gupta

---

Answer

When you query sys.databases or sys.master_files, you are always going to be querying the system view that lives in the master database, regardless of your current database context. This data is physically stored in the master database.

However, sys.filegroups is a system view for your current database, or the database you reference using a 3-part name. This data is not stored in master database, but in each individual database. For example, the following two queries are equivalent:

--Query 1
USE dbx;
SELECT * FROM sys.filegroups;
--Query 2
SELECT * FROM dbx.sys.filegroups;

If you need to access sys.filegroups for a given database, that database must be online & accessible so that your query can access the data stored in that database.

If your query says this:

USE MyData
GO
SELECT DatabaseName    = d.name,
       FileLogicalName = mf.name,
       FilegroupName   = fg.name
FROM sys.databases    AS d
JOIN sys.master_files AS mf ON mf.database_id = d.database_id
JOIN sys.filegroups   AS fg ON fg.data_space_id = mf.data_space_id;

The file group name is going to ONLY have filegroup names from MyData, not from every corresponding database. Only the rows for MyData will be correct, and everything else will have problematic data. It’s essentially doing this:

SELECT DatabaseName        = d.name,
       FileLogicalName     = mf.name,
       MyDataFilegroupName = fg.name
FROM master.sys.databases    AS d
JOIN master.sys.master_files AS mf ON mf.database_id = d.database_id
JOIN MyData.sys.filegroups   AS fg ON fg.data_space_id = mf.data_space_id; --Notice the DB name here

If you want accurate data for every database, you can use something like sp_ineachdb, and do something like this:

EXEC dbo.sp_ineachdb @command = N'SELECT DatabaseName    = d.name,
       FileLogicalName = mf.name,
       FilegroupName   = fg.name
FROM sys.databases    AS d
JOIN sys.master_files AS mf ON mf.database_id = d.database_id
JOIN sys.filegroups   AS fg ON fg.data_space_id = mf.data_space_id
WHERE d.database_id = DB_ID();';

The sp_ineachdb script is available for download as part of the First Responder Kit on github. You’ll find that there are a number of additional parameters that can be used to control exactly which databases are queried (or skipped) by the procedure.

answered 2021-11-15 by Andy Mallon