Exploring SQL Server table metadata with SSMS and TSQL

Phil shows how to start squeezing powerful magic from SSMS for doing a detailed exploration of the metadata of your routines and tables, in this third part to his series on exploring your database schema with SQL.

When you are using Management Studio, it would be nice to be able to select the name of an object, particularly a table, and to instantly see, at a keystroke, the build script in the results pane, complete with the documentation and, in the case of a table, the referential constraints. It would also be nice to get a list of all the places in the database where the object’s name appears.

A dream? I’ll show you in this article how to make this happen, and lots more.

First, a brief demo of what I mean.  I have a blank query window. I wonder what tables I have in my database and what they’re for.  I hit my query shortcut Control 6. This appears in the results pane.


We have  a list of tables for the database we are investigating (OK! It is AdventureWorks, i’ll admit). Hmm. We’ll pop them into the query pane in order to explore them. So, what is this table ‘ProductReview?. We hit Control 3 and instantly out it pops, the build script.


I have turned this query window into a powerful table-inspector, but I could have done it  for procedures, functions or triggers.  I can do other things, of course. How about telling me where the string ‘productReview’ occurs? Select it and hit cntl 4


How do I do this? There exists in SSMS a very useful facility for executing particular stored procedures or simple SQL expressions of your choice. There are a number of special keyboard shortcuts that are reserved for the user. These are called the Query Shortcuts and are accessed with ALT F1 Ctl F1, and Ctl 1 …Ctl 0. 


 Normally, you use these to invoke the system stored procedures sp_Help, sp_HelpText and sp_Who. The other nine slots are left enticingly blank for your own routines or queries. You can get in there and add your own, but these will only work in Query Windows that you open subsequently! 

Of course, for information such as that build script you’ll probably need to set the results window to text, and increase the no. of characters displayed in each column to a sensible level such as 8000 chars

If you highlight text in the code pane of the query window in SSMS or QA, and then invoke code that is slotted into one of these keyboard shortcuts, whatever you highlight is appended ‘as-is’ to what is executed. This means that if you want to pass a string to a procedure it will need to be a valid delimited string, escaped if necessary. If it is a valid object name, it is coerced successfully into a string. This means that you can pass the names of an object, but a qualified object name would have to be delimited properly.

Most of the code that I have in Query Shortcuts in my copy of SSMS is for getting lists of tables, procedures, parameters and so on. I haven’t hit a limit for the length of the expression, but it has to be all in one line. (see the screen-scrape of the Options tab above)

There are a number of queries that can go in one line that provide useful information before  you become forced to use a stored procedure. The sort of queries that work are the ones that don’t require parameters. 

There is a lot that can be done here like:….

…And a host of other queries you can work out from here. Of course, you can elaborate them. Here is some code that shows you all your functions along with their parameters, and any extended property: (but I won’t show it to you all in one long line as it will have to be for use)

With a moment’s thought, you’ll see a number of possibilities. In the past two articles in this series, I’ve given a few ideas: There are plenty more. What, for example, about listing out all tables along with a list of columns that can then be used for Select and update statements? Easy. Then you can just keep the list handy somewhere when doing some development work (I use AceText but you can use Notepad if you have nothing better). Here is the routine for the Table-lister I used earlier.

With a moment’s thought, you’ll notice that you can elaborate this to give you the complete select statement for tables, including all the comments, for all your database tables.  This is suddenly powerful magic, particularly as you can take out the new-lines and it all executes fine from a Query Shortcut key. 

This routine will create a select statement for every table in your database, including both table and column comments in extended properties. This will end up looking like this (Just one table in my sample)

You may wonder why I put the name of the table in comments at the start. This is so that it is easier to locate the table build script if your results pane is set to ‘grid’ view.


The script for Table-Valued Functions is even more complex, but this and the script for executing procedures can be useful if you take care to document your code using extended properties. (I use SQL Doc to make this easier to do). Your code starts looking a lot more readable and understandable.

Every grey-muzzled database programmer will have a ‘thumb-drive’ of favourite utility queries and routines to ease the development process. If you are clever with these, the requirement to continually poke and click all over SSMS to get anything done soon diminishes to a tolerable level and so your subsequent development work can get pretty much faster.

You’ll notice a catch when you want to get to the next level of complexity. We want to highlight the name of a routine or table and hit a keyboard shortcut to get a build script and see what is really going on.  We can’t use a query since the parameter ends up being appended. This is killer.  We have to use a stored procedure

Here we hit a particular problem, in that these keyboard query shortcuts are designed purely for use by system stored procedures, and they don’t easily lend themselves to use with normal stored procedures unless you propagate them to every database you are working on.  As a general practice, I put my kit of development tools in Model in a dev schema so it automatically gets propagated to all my development databases as I create them. However, this is useless for a keyboard-shortcut tool and it can end up being accidentally included in a deployment. You’d have thought that a safer alternative would be to create a special ‘Dev’ database for all your  metadata-pummelling tools, but this would mean that your tools could only be evoked for that database! The only alternative to placing your dev routines in each database is to put them in the MASTER database. We are faced with needing to make a special plea to the DBA to be allowed to do this, add the sp_ prefix, and register the stored procedure as a system stored procedure, but you would end up having to redo it on every service pack and upgrade. Putting routines into the MASTER database isn’t generally a good idea, but I’m afraid that this particular extension of SSMS requires it if you wish to have more than the standard development stored procedures like sp_help and sp_helptext.

The magic of searching for where a string occurs in a database was done with the procedure I gave you at the end of the first article in this series. You’ll find it here, but you’ll have to change its name by giving an ‘sp_’ prefix and put it in the master database, makking sure you register it too.

Why bother to look at table build scripts?

If you haven’t got SQL Prompt, table build scripts can be gotten from SSMS just by opening up the browser, clicking on the database, clicking on ‘tables’, and then right-clicking on the table you need information for.  Then you need to select ‘script tables as’ and finally choose a suitable target.  When you finally get the table-build script, you’ll see that it isn’t designed for humans to see. The comments (MS_Description) for each column aren’t shown with the table, and the description of the table is lost half-way down the page. It isn’t usually clear which columns are foreign keys and what they are referring to. (they don’t use the clearer ‘REFERENCES‘ syntax for single-column foreign key constraints) It isn’t programmer-friendly. You’ll soon detect that doing things this way is fine for the database of your CD collection but not much else. For serious exploration of metadata, you need something much better. Ideally, of course, you’ll have everything to hand using SQL Doc, or some other third-party documenter, but the method I’m describing isn’t bad, and can be honed to your exact requirements..

 Under the hood.

 With stored procedures, views, triggers and functions, SQL Server stores the source. This is easy to fetch out.

If fetching table scripts were that easy, you wouldn’t need the rather scary script at the end of this article. However, tables aren’t held in script form in SQL Server because it would be difficult to synchronise the script with any changes you made with its child objects such as columns or constraints. SSMS uses SMO to reconstitute the build script. It is an elaborate process. Unike MySQL, there is no SQL command to produce a build script. Either we have to use SMO, or hand-craft a stored procedure to do it.

There is a good reason why table-build scripts do not proliferate in SQL Server Blogs. They are hard to get right, and they’re a moving target with every revision of SQL Server. Here is my take on the problem, which aims to provide the script for any object. Remember, please, before you use this, that these are intended to allow you to get information about your objects such as tables, functions, procedures and so on. The table section, in particular will not give you a complete build script as I don’t bother with indexes. Oh no, this is for looking at.

So all you need to do now is to collect up the other scripts you find useful and configure up your SSMS Query Shortcuts to give you extra speed for your database development work, especially if you are refactoring someone else’s database. The reason I like doing this sort of thing is because I like to hone my development environment to my own particular tastes. Your tastes will be different, but I hope you agree with the principle that it is good to take some time to make sure you can develop things quickly and without frustrating delays. There is nothing more frustrating than wrestling with an IDE designed by people who don’t seem to understand how database developers do their work.

See also the first two articles in this series
      Finding Stuff in SQL Server Database DDL
      Exploring your database schema with SQL