Tuesday, March 22, 2011

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…

System Object Query Result in SSMS

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…

System Object Query Result in XML Window

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:

System Object Information in Browser Window

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.

2 comments:

  1. Sorry, but I'm not going to add Kendra's blog to my RSS feed. (Just to avoid redundancy, the feed's already there)

    BTW, I plan to try to make "Schulzified" part of my working vocabulary.

    ReplyDelete
  2. Oh, this is BRILLIANT.

    I was looking at doing something like this last night, but couldn't conceptualize how to present it, *and* I didn't know that trick about CTRL+Click.

    Magical, as usual!

    ReplyDelete