“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 (http://blog.sqlauthority.com)