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.
Parv The IT Geek 