“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
)
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
Pingback: SQLAuthority News – Monthly Roundup of Best SQL Posts Journey to SQL Authority with Pinal Dave
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
Pingback: SQL SERVER – Weekly Series – Memory Lane – #029 | SQL Server Journey with SQL Authority
could someone answer sanath kumar question…