“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
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)
21 Comments. Leave new
Do you know a book that have dmv’s detailed ?
Yes. It is called Books On Line (SQL Server help file)
Hi Fabrico
do you want dmvs book this is the book
“Performance Tuning with SQL Server Dynamic Management Views”
Thanks.
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
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?
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
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.
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
Hi Dave,
Can you explain how to apply patch and hot fixes in cluster environment.
How can I list all the database scoped DMVs?
Nice article! Very useful information!
Thanks!
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.
How we differentiate DMV and DMF from the query
could someone answer sanath kumar question…
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.
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
Hi dave,
could you tell me what is the use of DMVS .when do we actually use this
@Kenny – DMVs are used to get information from SQL server.