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.