Saturday, June 26, 2010

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
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 */
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.

Object Query With Hyperlinks

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)?


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:

Query with Hyperlinks to DDL Code

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

XML Window Opened By Hyperlink

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.

Code Window created from the XML Window

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.


  1. Wow,

    Thanks for posting this, I am going to play with it now.

  2. That is the coolest thing! Thank you for posting it!

  3. Wow Brad, this is very flattering, I really enjoy the role of catalyst :)
    This is really cool script!
    Now we need to catalyze someone - maybe Mladen - who will hook up your script to a context menu in SSMS :)

  4. Brad,

    it needs a small change.
    For nchar and nvarchar datatypes length should be divided by 2.

    The rest looks cool. Thank you.

  5. Aaaaacckk! Thanks for catching that, Leonid. It's been fixed and an updated version is at the SkyDrive.

  6. Thanks for all the comments, everyone... I'm glad you liked the script!

  7. Hey Brad, how about adding in permissions?

    (/* Get Permissions for Table */
    char(13) + char(10)
    + [perms].[state_desc]
    + ' '
    + [perms].[permission_name]
    + ' ON '
    + QUOTENAME(OBJECT_SCHEMA_NAME([perms].[major_id]))
    + '.'
    + QUOTENAME(OBJECT_NAME([perms].[major_id]))
    + ' TO '
    + QUOTENAME([prin].[name])
    sys.database_permissions AS [perms]
    sys.database_principals AS [prin]
    ON [perms].[grantee_principal_id] = [prin].[principal_id]
    [t].[object_id] = [perms].[major_id]
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
    , 1, 2, '')
    ) AS [Permissions]([PermissionList])
    cross apply ( /* Set up the DDL Link */
    select [DDL Link]=(select [processing-instruction(q)]=
    'create table '+TableName
    +char(13)+char(10)+' '+ColumnList
    +coalesce(char(13)+char(10)+' /* Check Constraints */'
    +coalesce(char(13)+char(10)+' /* Primary Key */'
    +coalesce(char(13)+char(10)+' /* Foreign Keys */'
    +coalesce(char(13)+char(10)+'/* Non-Primary-Key Indexes */'
    +COALESCE(char(13)+char(10)+'/* Permissions */'
    +char(13)+char(10)+[PermissionList], '')
    for xml path(''),type)) F_Link

  8. Check