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:



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:



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:



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



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:



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:



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



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:



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:



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.