Thursday, June 17, 2010

Hyperlinks To T-SQL Code

There are often questions on the MSDN T-SQL Forum regarding how you can find all stored procedures (and/or functions and/or triggers and/or views) that contain a particular string. Thankfully, the object_definition() function gives us the ability to acquire the T-SQL code of those objects and we can easily find a particular search string in that code.

For example, the following query will look through all the objects (sys.objects) in the AdventureWorks database, looking for procedures (type=’P’) and views (type=’V’) and triggers (type=’TR’) and functions (types ‘FN’, ‘IF’, ‘TF’) that contain the string ‘ContactTypeID’:

select ObjType=type_desc 
,ObjName=schema_name(schema_id)+'.'+name
,ObjDef
from sys.objects
cross apply (select ObjDef=object_definition(object_id)) F1
where type in ('P' /* Procedures */
,'V' /* Views */
,'TR' /* Triggers */
,'FN','IF','TF' /* Functions */
)
and ObjDef like '%ContactTypeID%' /* String to search for */
order by charindex('F',type) desc /* Group the functions together */
,ObjType
,ObjName
I use a CROSS APPLY to introduce a column called ObjDef, which contains the full object_definition() value (i.e. the T-SQL code) of the object. This way I can reference ObjDef in my WHERE clause and in the SELECT list. And if I want to search for a second string, I can simply add a AND ObjDef LIKE ‘%otherstring%’ predicate to the WHERE clause.

I also sort the output so that the rows are grouped by the type of object and then, within each type, the rows are sorted by the name.

And that gives us the following result:

Boring Object Query

This is very nice to get this all at a glance, but the ObjDef column is limited. I can widen the column in the grid, but only so far. And the contents don’t contain any of the newline characters… it’s just one looonnngggg string of text that I can’t read. I could copy/paste the contents into Excel, but again, it will just be a single line of text with no newline characters. And even so, SSMS will not output any more than 65536 characters in a column in a grid result window, so we may not get the full code anyway.

We could output to a text window, which will retain the newlines, but the maximum characters per column that we can output is 8192. Plus the output is ugly.

So what can we do, outside of a lot of searching and pointing-and-clicking in the Object Browser, to see the code for these objects?

Well, MVP Adam Machanic had what I thought was a brilliant idea in how to accomplish this in his sp_who_is_active procedure. The answer is XML. XML columns have two great features. First of all, you can bump up the maximum character output of XML to be unlimited if you wish:

Query Options Dialog

And second of all, XML columns are conveniently presented as hyperlinks in Grid Output.

An unfortunate side-effect of converting text to XML, though, is that XML will encode characters like less-than and greater-than and ampersand to < and > and & respectively. But Adam cleverly uses the processing-instruction() XPath function, which will bypass the encoding and, more importantly, will preserve all the newlines and indentions exactly as is.

So here is a revised copy of our query to find ‘ContactTypeID’ in AdventureWorks, with a new column called ObjDefLink created via the processing-instruction() XPath function in a second CROSS APPLY:

select ObjType=type_desc 
,ObjName=schema_name(schema_id)+'.'+name
,ObjDefLink
from sys.objects
cross apply (select ObjDef=object_definition(object_id)) F1
cross apply (select ObjDefLink=(select [processing-instruction(q)]=ObjDef
for xml path(''),type)) F2
where type in ('P' /* Procedures */
,'V' /* Views */
,'TR' /* Triggers */
,'FN','IF','TF' /* Functions */
)
and ObjDef like '%ContactTypeID%' /* String to search for */
order by charindex('F',type) desc /* Group the functions together */
,ObjType
,ObjName
The processing-instruction(q) will put our object definition code between <?q … ?> delimiters, but, as I mentioned, it’s all presented as a hyperlink, as you can see below:

Exciting Object Query with Hyperlinks!

Let’s click on the hyperlink in the second row to see the code of the Purchasing.vVendor view in a new window:

XML Window Opened by Hyperlink

Looks great! I can see all the code for that view, but it’s in a drab gray color, since that’s how an XML window colors any processing-instruction tag.

If you prefer to see the code with all the usual syntax coloring in a T-SQL window, it’s just a matter of a few keyboard shortcuts: CTRL+A (to Select All), CTRL+C (to copy to the Clipboard), CTRL+F4 (to close the window), CTRL+N (to open a new query window), and CTRL+V (to paste the contents into that window). And then remove the <?q … ?> delimiters from the beginning and the end, and voila… there you see the code in all its glory:

Code Window created from the XML Window

This method can come in handy in several ways.

For example, rather than showing individual rows for the objects whose code contains a certain string, let’s instead just create a single hyperlink to ALL the code that contains the string. Here’s how:

declare @Script nvarchar(max) 
select @Script=(select '
/*
'
+replicate('=',100)+'
'
+schema_name(schema_id)+'.'+name+' ('+type_desc+')
'
+replicate('=',100)+'
*/'
+ObjDef+'
GO
'
from sys.objects
cross apply (select ObjDef=object_definition(object_id)) F1
where type in ('P' /* Procedures */
,'V' /* Views */
,'TR' /* Triggers */
,'FN','IF','TF' /* Functions */
)
and ObjDef like '%ContactTypeID%' /* String to search for */
order by charindex('F',type) desc /* Group the functions together */
,type_desc
,schema_name(schema_id)+'.'+name
for xml path(''),type).value('.','nvarchar(max)')

select CodeLink=(select [processing-instruction(q)]=@Script
for xml path(''),type)
First, I populate a @Script variable, concatenating it with the code of each object, along with some comment header information I supply that contains the object’s name and its type, and I follow each code chunk with a GO command. (For an explanation of the FOR XML PATH and TYPE and .value() stuff in the code, please see my blog post entitled Making a List and Checking It Twice).

Then, the second query simply creates a single-row single-column processing-instruction XML link out of that variable. Here’s what the result looks like in the Grid Results window in SSMS:

Object Query to produce hyperlink to code of ALL objects

And when you click on that hyperlink, you get all the code (of all 3 objects… the function and the two views):

XML Window Opened by Hyperlink

And, again, with a quick CTRL+A, CTRL+C, CTRL+F4, CTRL+N, CTRL+V, and a couple DELETE keypresses, we get the code with syntax coloring, ready for examination and possible modification:

Code Window created from the XML Window

You can also incorporate these code hyperlinks into your DMV queries. For example, here is a query that I acquired from MVP Glenn Berry and tweaked a little bit to include a couple additional columns that I wanted, including the hyperlink column to the code. It uses DMV’s to look into the procedure cache and presents the top 50 queries in descending order of Average CPU time… in other words, the most expensive queries in terms of CPU:

select 
top 50 [Database]=coalesce(d.name,'AdHoc')
,CodeLink=(select [processing-instruction(q)]=qt.[text]
for xml path(''),type)
,TotWorkTimeMS=cast(qs.total_worker_time/1000.0
as decimal(12,2))
,AvgWorkTimeMS=cast(qs.total_worker_time/1000.0/qs.execution_count
as decimal(12,2))
,ExecCount=qs.execution_count
,[Calls/Second]=coalesce(qs.execution_count
/datediff(second,qs.creation_time,getdate())
,0)
,AvgElapsedTimeMS=cast(coalesce(qs.total_elapsed_time/1000.0/qs.execution_count,0)
as decimal(12,2))
,MaxLogReads=qs.max_logical_reads
,MaxLogWrites=qs.max_logical_writes
,CacheAgeMins=datediff(minute,qs.creation_time,getdate())
,QueryPlan=qp.query_plan
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) qt
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
left join sys.databases d on qt.dbid=d.database_id
order by AvgWorkTimeMS desc
And here is the result:

Most Expensive Queries

So the code that produced each of the high-CPU queries is just a click away.

I hope you find all this as useful as I do.

Update Jun26,2010: Check out my next blog entry, where I show how to provide hyperlinks to DDL (CREATE TABLE) code.

7 comments:

  1. Hey Brad, this is just awesome. This will solve many of my problems...
    Thanks for sharing.

    ReplyDelete
  2. Thanks, Michael...

    After you posted your comment, I made a slight tweak to the code that produces the single hyperlink to the code of ALL objects so that it orders them correctly.

    --Brad

    ReplyDelete
  3. Hi Brad,

    Good post!! This method has been invaluable to me in my day to day job, especially when looking at dmvs. One thing I do in my environment is use a correlated subquery, oppose to cross apply, and make sure to name the column as [text()]. Naming the column as such removes the unneeded tags.

    e.g.
    select mycolumn,(select othercol as [text()] for xml path(''),TYPE) as OtherCol

    ReplyDelete
  4. Hi Adam...

    The text() method seems more appealing because of the lack of tags, BUT it has the unfortunate encoding side-effect... i.e. it encodes any less-than and greater-than and ampersand characters... so you don't get the "real" original code.

    --Brad

    ReplyDelete
  5. Hi Brad,

    This is very true and I have been using the code [text()] quite succesfully for the past year or so, not really knowing about processing-instruction. the processing-instruction is a brilliant new take on preserving special characters. I will probably adopt the new technique as it really beats find/replace all special characters.

    ReplyDelete
  6. This is great trick Brad, thanks. It's a pity that table definitions cannot be acquired in similar way.

    ReplyDelete
  7. @Piotr...

    Thanks for your comments.

    I took your "pity" comment about table definitions to heart... I put together code to create hyperlinks to table DDL code and that will be my next blog post.

    Thanks for a great idea!

    --Brad

    ReplyDelete