How to list all SQL DMVs (Dynamic management views) s and DMFs (Dynamic management functions ) in…

Have you ever wondered how many DMVs are in SQL? Wondered whether a DMV exists which help diagnose your problems? Well I am going to show you how to list all the DMVs and DMFs so that you can do a deeper dive into them or await my series where I will be exploring each every DMV and DMF.

Before you run the script it is important to understand that Dynamic Management Views and Functions are used to return information about the status of SQL Server so that you can performance tune issues, check and diagnose for problems.

WHERE [name] LIKE '%dm_%'
AND [type] IN ('V', 'TF', 'IF')
ORDER BY [name]

This script will only run in SQL2005 ( SQL 2000 does not include these features) and above.

In SQL 2008R2 there are 141 DMVs and DMFs and in 2012 there are 171.

Originally published at on January 25, 2014.


Published by Parvinder Nijjar

I blog at

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: