Total number of DMV in SQL Server

Question

Post reply

Total number of DMV in SQL Server

Shadab Shah

SSCarpal Tunnel

Points: 4759
More actions
November 21, 2012 at 6:44 am

#271136

Hi all,
Since there are number of DMV's present and there is no proper presentation on it. So if suppose some has to see the number of DMV's present in SQL Server, then how to see it.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

the # depends on the SQL server version, and in some cases, the Service Pack version as well.

easiest way is to simply look in SSMS Object Explorer>>Expad Server>>System Databases>>master>>Views>>System Views

Or via TSQL:

select * from master.sys.all_views where schema_id=4

on my SQL2005, i have 264 dmv's in that list.

on my SQL2008, i have 334 dmv's in that list.

on my SQL2012, i have 379 dmv's in that list.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 2

Thanks Lowell. I exceuted your query and found some of the column name not preceding with dm_...... . So is that OK?

Lowell SSC Guru Points: 323518 More actions · Answer 3

Shadab Shah (11/21/2012)
Thanks Lowell. I exceuted your query and found some of the column name not preceding with dm_...... . So is that OK?

well you can filter it from there...I just wanted to demo how to find all the system views you should be aware of.

The dm_ is a naming convention to help draw your attention,but many of the other views that appear on that list are worthy of a DBA notice, regardless of whether the name starts with dm or not.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!