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.

SELECT * 
FROM SYS.ALL_OBJECTS
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 https://parvtheitgeek.com on January 25, 2014.

Published by Parvinder Nijjar

I blog at ParvTheITGeek.com

Leave a Reply

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

WordPress.com Logo

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

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s

<span>%d</span> bloggers like this: