More Fun With Hyperlinks: DDL Code

In my last blog entry, I demonstrated some queries that will produce results with hyperlinks to T-SQL Code. For example, the following query will find all procedures, views, triggers, and functions in AdventureWorks that contain the string ‘ContactTypeID’. The hyperlinks are created via the processing-instruction() XPath function. You can get a detailed explanation of how it works in my previous blog entry.

use AdventureWorks
go
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
 

This produces the output below in the Grid Results window. Clicking on any of the hyperlinks will bring up the code for that object in a new window.



Piotr Rodak (who has a very nice blog, and he also has, by far, the most clever blog name in existence) left a comment on my last post saying, “It’s a pity that table definitions cannot be acquired in a similar way.”

Wow, what a great idea! Imagine yourself walking into a new client (or new job) with a database with hundreds of tables and no documentation anywhere. Yes, you could right-click on the database in Object Explorer and choose Tasks -> Generate Scripts… from the popup menu and go through all the dialogs, and then generate a single code window or a single file or (if you have SQL2008) separate files for each object.

But instead, how about a query that produces a list of tables in the database, along with a hyperlink to the DDL Code for the table (and all its indexes)?

Coo-ul.

I was up for the challenge, and so I put a (looonngg) query together to do just that. Using the Object Catalog Views (i.e. sys.tables, sys.columns, etc), it generates the vast majority of the DDL Code for a table… the only features it leaves out are anything that has to do with Data Compression, Sparse Columns, Column Sets, FileStream, and Partitioning. Some things I left out because of time… other things I left out because they were SQL2008-only features and I wanted the query to work in both SQL2005 and SQL2008.

Here is the output of the query for the AdventureWorks database:



And, if we click on the hyperlink for the HumanResources.EmployeeDepartmentHistory table, for example, we get the following (in an XML window):



And to get the syntax coloring in a new code window, we perform a couple of keystrokes: CTRL+A (Select All), CTRL+C (Copy), CTRL+F4 (Close Window), CTRL+N (New Query Window), CTRL+V (Paste), and then a few DELETE keystrokes to get rid of the XML delimiters at the beginning and the end, and there’s the code for the creation of the table. Note the columns, their defaults, the check constraints, primary key constraint, foreign key references, and the (non-primary-key) index definitions for the table.



The code for this query is too long to incorporate here in this blog article, but you can download it from my SkyDrive. It’s just a single query, so you can easily incorporate it into a stored procedure if you wish.

Thanks again to Piotr for his comment that acted as the catalyst for this idea. I hope you find it to be helpful.

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.

My Favorite SQL2008 Feature

This blog entry is participating in T-SQL Tuesday #007, hosted this month by Jorge Segarra.

You are invited to visit his blog to join the party and read more blogs participating in this month’s theme: Your favorite hot new feature in the SQL2008 R2 (or SQL2008 in general) release.

It certainly was hard to narrow it down to one new feature that really made me excited…

…but here's what I came up with:

---

For me, the coolest feature of SQL2008 is_member definitely Intellisense… specifically the statement completion aspect. It's a really cool newfilestreamvalue feature!

When I program_name Tan-SQLvariant, Intellisense helps me out by completing the words that I type_id. It's almost like it has_dbaccess E.S.P. original_db_name something… it somehow knows exactly what I want todatetimeoffset say!

When I comparecompressedscalars my productivity between SQL2005 and SQL2008, I am soundex glad that I made the switchoffset to SQL2008. Once I connectionproperty to a serverproperty and point to a database_principal_id, the names of the columns_updated pop up ascii I type the textptr of my queries. And key_guid words are automatically completed too! I no longer have to worry about an error_line index_col my code. No more need to verifysignedbyasymkey that I typed everything correctly. That's the really exciting partition_fragment_id! It's fantastic!

So if you are still using SQL2005, don’t be dense_rank! Act now and make the change_tracking_current_version to SQL2008. Get_filestream_transaction_context update-to-dateadd with the current_request_id technology! You'll be happy you did. Intellisense has_dbaccess made it all worth it.

And you can quotename me on that!