SQL SERVER – List All the DMV and DMF on Server

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

The answer is very simple:

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

SQL SERVER - List All the DMV and DMF on Server dmvnames

Update: Madhivanan has corrected the script here.

DMVs can be referenced in T-SQL statement by multi part names. DMF on the other hand can not be referenced in four part name. DMV and DMF both cannot be referenced in T-SQL statements by using one part names. All DMVs and DMFs exist in the sys schema and follow this naming convention dm_*. When you use a DMVs or DMFs , you must prefix the name of the view or function by using the sys schema.

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

Solarwinds
, ,
Previous Post
SQL SERVER – Four Posts on Removing the Bookmark Lookup – Key Lookup
Next Post
SQLAuthority News – Storage and SQL Server Capacity Planning and configuration – SharePoint Server 2010

Related Posts

21 Comments. Leave new

  • Fabricio Lima
    May 18, 2010 6:43 pm

    Do you know a book that have dmv’s detailed ?

    Reply
  • 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

    Reply
  • גרי רשף
    May 21, 2010 1:58 pm

    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?

    Reply
    • Are you sure you dont get correct results?

      Try this too

      SELECT name, type, type_desc
      FROM sys.system_objects
      WHERE name LIKE ‘dm[_]%’
      ORDER BY name

      Reply
      • גרי רשף
        May 21, 2010 10:42 pm

        LIKE ‘dm[_]%’
        Is correct!

      • Hi,
        I am a newbie. I got the same answer for both the queries i.e. query provided by you and the one provided by madhivan. So what’s the difference

    • Replaced in which version? Works for me in 2008.

      Reply
  • Here’s a nice forthcoming DMV book from manning, the first chapter is free (and contains the above tip about how many DMVs are there…)

    http://www.manning.com/stirk

    Reply
  • Hi Dave,

    Can you explain how to apply patch and hot fixes in cluster environment.

    Reply
  • How can I list all the database scoped DMVs?

    Reply
  • Evandro Junqueira Ramos
    May 21, 2011 8:50 am

    Nice article! Very useful information!
    Thanks!

    Reply
  • 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.

    Reply
  • Sanath Kumar
    June 21, 2012 2:52 pm

    How we differentiate DMV and DMF from the query

    Reply
  • Deepak Reddy
    June 12, 2013 9:09 pm

    could someone answer sanath kumar question…

    Reply
  • I couldn’t find any Dynamic views(DMV) or Dynamic functions(DMF) in sql server 2000 and these seems to be available from sql server 2005 only.

    Reply
  • Hi Dave,

    I have modified the script and filtered 117 DMVs which is used in SQL 2008.
    SELECT name, type, type_desc
    FROM sys.system_objects
    WHERE name LIKE ‘dm_%’ and type=’V’
    ORDER BY name

    Reply
  • Hi dave,

    could you tell me what is the use of DMVS .when do we actually use this

    Reply

Leave a Reply

Menu