SQL SERVER – List All the DMV and DMF on Server

“How many DMVs and DVFs are there in SQL Server 2008?” – this question was asked to me in one of the recent SQL Server Trainings.

Answer is very simple:

SELECT name, type, type_desc
FROM sys.system_objects
WHERE name LIKE 'dm_%'
ORDER BY name

Update: Madhivanan has corrected the script here.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

20 thoughts on “SQL SERVER – List All the DMV and DMF on Server

  1. Alternatively we can use the following query:

    SELECT name, type, type_desc from sys.all_objects WHERE name LIKE ‘dm_%’
    ORDER BY name

    Fabicio: for your answer I would suggest that you refer to microsoft technet site. I find it the best till date for information on DMVs.

    Regards
    Amit Pandey
    SQL Server Database Administrator

  2. As you surely know- the ‘_’ Char replaces a single char when one uses the *Like* operator, and thus you will get all objects begining with ‘dm’ and having at least 3 chars..

    I would try-
    WHERE Left(name,3)=‘dm_’
    Other suggestions?

  3. Pingback: SQLAuthority News – Monthly Roundup of Best SQL Posts Journey to SQL Authority with Pinal Dave

  4. Hello Pinal,
    I enjoy reading you posts. I think they are v informative, easy to understand and the best part is easy to implement. My comment is not for this post it is for sqlauthority on the whole. Keep up the good work.

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #029 | SQL Server Journey with SQL Authority

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s