------------------------------------------------------------------------------------------------------------------------------------------
TITLE: Searching DMFs and DMVs
CANONICAL URI: https://www.totalsql.com/sql-server-content/tips/searching-through-sql-server-dmv-and-dmf-names
AUTHOR: Michael K. Campbell 
COPYRIGHT: 2026 All Rights Reserved: OverAchiever Productions, LLC. 
RELEASED: 2024-09-13
UPDATED: 2026-10-19
------------------------------------------------------------------------------------------------------------------------------------------

# Searching DMFs and DMVs

## Spelunking through SQL Server Meta-Data
Every once in a while, when I'm trying to solve some sort of problem that has to to with tuning, optimization, or some other bit of nitty-gritty that involves SQL Server Internals, I find it nice to be able to search through DMVs and DMFs for any column-names that might overlap with whatever it is I'm looking for. 

Granted, if your google-fu is solid enough, you can probably find what you need without this, but:
- sometimes my google-fu isn't as strong as it needs to be
- or, at other times, whatever I'm searching for has too many overlaps with something that has been blogged or documented to the point where there's SO MUCH NOISE that I simply can't quickly find what I'm looking for. 
## DMVs to the Rescue
In cases like the above, I've found that the following query works quite well to let me know if there's something I might be able to "dig into" a bit further - with a very minor amount of effort (i.e., just have to get the POTENTIAL column-names/matches right, and then I'm usually able to find a few rabbit-holes):

```sql 

SELECT 
	OBJECT_NAME([c].[object_id]) [dmv], 
	[c].[name] [column_name] 
FROM 
	sys.[all_columns] [c]
WHERE 
	[c].[object_id] < 0
	AND [c].[name] LIKE N'%lsn%'
ORDER BY 
	OBJECT_NAME([c].[object_id]);

```
e.g., in the query above, I'm looking for anything that might give insight into LSNs - or, I could change the above to something like `%clock%` if I'm digging for info on cache pressure, and so on. 

## What's Taters, Precious?
The query above is honestly NOTHING SPECIAL, just a simple query against `sys.all_columns` (as `sys.columns` ONLY returns information about (user AND system) tables, views, and functions - whereas `sys.all_columns` pulls back EVERYTHING - including columns within DMVs and DMFs) - and then a simple WHERE clause to predicate such that only parent objects with a NEGATIVE `object_id` are returned (so that you're only searching amongst DMFs and DMVs).

Note that I'm not aware of any **explicit** documentation that says that DMVs/DMFs use negative `object_id`s, but they DO (as you can see if you take this for a test drive), and it's worth pointing out that `OBJECT_NAME()` ALSO has no problems with lookups against negative `object_id`s, e.g., 

```sql

SELECT OBJECT_NAME(-537);

```

So, presumably, DMFs and DMVs are (obviously) OBJECTS, but to prevent them from 'interfering' with outputs of 'normal' (user) objects, someone at Microsoft at some point took the approach of assigning them negative ids.

And, ironically enough, I'm guessing that this insight into SQL Server internals is **probably documented somewhere by Microsoft** - my google-fu just hasn't been strong enough to find it.