DMV Queries - Get DMV/DMF List

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    ChillyDBA
Date:        29 Jun 2012

Description

Since their introduction in SQL Server 2005, DMVs/DMFs have become a staple for DBAs who want/need to examine in-depth aspects of SQL Server management and structure.
Each new SQL Server version (and sometimes Service Pack) broadens the scope of these valuable assets.  SQL Server 2008 R2 now has in excess of 130.

It can sometimes be a challenge to find exactly the information that you require, as SQL BOL is not really designed for just browsing across the collection of DMVs.
I therefore created a quick query to list all the DMVs and DMFs along with columns and data types.  This is easier to browse initially, with BOL providing the in-depth information once the required DMV/DMF has been identified.
 

Code

--Listing all the DMV/DMF along with its columns, their 

--data types and size

SELECT
  
SCHEMA_NAME(so.schema_id)   AS SchemaName,
  
so.name                     AS DMVName,
  
sc.name                     AS ColumnName,  
  
t.name                      AS DataType,
  
sc.column_id                AS ColumnOrder,
  
sc.max_length               AS MaxLength,
  
sc.PRECISION                AS PRECISION,
  
sc.scale                    AS Scale
FROM sys.system_objects so
INNER JOIN sys.system_columns sc
  
ON so.OBJECT_ID = sc.OBJECT_ID
INNER JOIN sys.types t
  
ON sc.user_type_id = t.user_type_id
WHERE so.name LIKE 'dm_%'  
ORDER BY so.name, sc.column_id

ċ
Get List of all DMVs.sql
(1k)
Andy Hughes,
Jun 29, 2012, 7:57 AM
Comments