DMV/DMF Info Just A Couple Clicks Away

If you’re not reading Kendra Little’s blog, then you’re really missing out. It’s full of terrific technical information, but it’s her artwork and sense of humor that really make it special.

This morning, Kendra wrote a really cool post called Dynamic Management Quickie: Exploring SQL Server’s System Views and Functions As You Work.

It included a query that will help you find system views and/or functions that have a column name containing a certain search string. For example, to find stuff that has to do with CPU, you can execute the following query (which I rewrote/reformatted in Schulzified style):

select SysObjName
      ,o.type_desc
      ,ColumnName=c.name
from sys.system_columns c
join sys.system_objects o on c.object_id=o.object_id
cross apply (select SysObjName=schema_name(schema_id)+'.'+o.name) F_SON
where c.name like '%cpu%'  --Enter Search String Here
order by SysObjName
/*
SysObjName            type_desc                         ColumnName
--------------------- --------------------------------- ---------------
sys.dm_exec_requests  VIEW                              cpu_time
sys.dm_exec_sessions  VIEW                              cpu_time
sys.dm_os_schedulers  VIEW                              cpu_id
sys.dm_os_sys_info    VIEW                              cpu_count
sys.dm_os_sys_info    VIEW                              cpu_ticks
sys.dm_os_sys_info    VIEW                              cpu_ticks_in_ms
sys.fn_trace_gettable SQL_INLINE_TABLE_VALUED_FUNCTION  CPU
sys.syslogins         VIEW                              totcpu
sys.sysprocesses      VIEW                              cpu
*/

This is really helpful, but Kendra went one step further and posted another query which added a column containing a URL so that you could open a browser window (CTRL+ALT+R) and paste the URL in to get more information on the subject.

I thought this was a great idea, but, lazy person that I am, I wanted to cut down on the number of clicks and keystrokes that I had to do in order to get to the browser window... and I wanted all the information there so it was just one click away.

So I came up with the following query:

with SysObjInfo as
(
  select SysObjInfo=(select nchar(13)
                           +N'System Object:    '+SysObjName+nchar(13)
                           +N'Type of Object:   '+o.type_desc+nchar(13)
                           +N'Column Name:      '+c.name+nchar(13)
                           +N'URL (CTRL+Click): '
                           +N'http://social.msdn.microsoft.com/'
                           +N'Search/en-US/?Refinement=117&Query='
                           +SysObjName
                           +nchar(13)
                     from sys.system_columns c
                     join sys.system_objects o on c.object_id=o.object_id
                     cross apply (select SysObjName=schema_name(schema_id)+'.'+o.name) F
                     where c.name like '%cpu%'  --Enter Search String Here
                     order by SysObjName
                     for xml path(''),type).value('.','nvarchar(max)')
)
select LinkToSysObjInfo
          =(select [processing-instruction(q)]=N':'+nchar(13)+SysObjInfo+nchar(13)                                 
            from SysObjInfo
            for xml path(''),type)

You can find the explanation for the processing-instruction directive and all the other XML stuff in an article I wrote last June called Hyperlinks To T-SQL Code.

When I execute that query in SSMS, it gives me a single hyperlink…



When I click on that hyperlink, it opens an XML window with a list of all the views/functions that have to do with CPU…



If I see one that interests me and I want to learn more, I just CTRL+Click on the URL and voila! There’s a browser window with MSDN Search giving me links to the view/function:



So now all the information you want on views/functions on a certain subject are just a couple clicks away.

Thanks again to Kendra for the original idea.

Now do yourself a favor and add her blog to your reader right now. Then sit back in your easy chair by the fire and enjoy catching up on her posts… Make yourself comfortable because you won’t be able to stop.