What’s in that database? Getting information about routines

There is a lot of information held in SQL Server metadata about your SQL Server procedures, triggers, views and functions. Some of it is valuable, other nuggets are useful and a few are rather dull but worthy. It really all needs to be tied together to tell the full story, especially if you are not able to, or do not want to, query the metadata directly. Until JSON arrived, this information could be viewed but it was all a bit tedious to get and display because it was hierarchical in nature. Now we can do a lot better in getting all this information about our database’s routines. (Routines, including triggers, views, procedures, Replication filter procedures, rules, defaults and functions, are also referred to as modules). Once we have the information, there are plenty of ways of inspecting it or getting reports from it.

We know from the metadata the identity of the other routines that use our routine, the objects that it references, its settings, parameters, return value, the table source it produces, any documentation in the extended properties. It is great to know all this when you are developing or maintaining SQL Server code. Over the years, I’ve used various approaches to getting all this information. Nowadays, I generally get a JSON document that has all the routines I’m interested in.

Here is an example, using AdventureWorks2016, where I’ve got the details of just one of each type of module contained there. The descriptions of the routines and the inline comments on the columns, and parameters come from the MS_Description extended property

This is very useful for documentation purposes, of course and the information about each routine would normally be added to the header of the source code for that routine. To do this, it is often better for visibility to convert it to YAML which doesn’t have to be ‘beautified’ to make it readable and indented. It is also easy to pop it into a JSON-oriented database such as MongoDB to get reports on your code.

The JSON data above is easily queried to provide reports. This simple code, for example …

… would produce this. The @json variable contains the JSON data above.

How do we get this JSON? It is also easy to get started with this sort of thing. If we do this:

we get something like this (first six records only)

There is a lot more than this that I want to know, though.  This article is really about how to drill in further into the detail. The code that I generally use to generate the JSON report of a database’s routines is a single, rather large, query or temporary stored procedure. I do this because it doesn’t require the database to be modified. Here in this article I break it into manageable Table-valued functions that can be ‘inlined’. The problem with this is that they aren’t really designed for this sort of work because they have to be created in the database you are documenting and deleted after use. They therefore have to be eliminated from the set of modules being documented. You can use temporary stored procedures but not functions. I generally gloop all the code into a rather terrifying query, because I’ll be doing it as a repeatable script in PowerShell. It leaves no trace in the database being documented, of course. It also allows me to do the conversion to YAML more easily, allows me to update headers and so on.

For the purposes of this article, I’ll use the TVF approach, because one can explain it incrementally. To use this, you have to remember to delete it once you’ve finished. Some developers use a schema especially for such utilities that is not put into source control. I generally use temporary stored procedures for this sort of work but you can’t have temporary functions and this code relies heavily on Table-valued functions.

We’ll work through some of the routines that are required to give us a report on a database that we wish to document.

Getting a list of the names of objects referenced by this routine

We start by collecting the names of the objects, If any, that are referenced by the routine

This only returns a string as a single row containing a single column. It must access a table, the sys.sql_expression_dependencies system view. It is capable of improvement, I reckon: this is easy with a modular construction. Should these lists actually be lists of both names and object descriptions? When scanning them it isn’t always obvious, with many databases, what type of object it is. Be my guest and do it!

This function isn’t a huge step forward by itself but here is how you could use it to get a JSON report on all the modules in the database that make references to other objects such as tables. For these, it provides a References key that has as its value a list of the names of all the objects it references.

Getting a list of the names of objects that reference the routine

This routine is even simpler. It simply gets the names of all the objects that reference the routine.

You can easily use this in a query to list out those routines that are referenced by other objects and with them the names of the referencing objects. As a variation, we’ll use it to get a document and then convert it back into a relational table just to illustrate the flexibility of JSON.

Of course, it is much easier to get this result from sys.sql_expression_dependencies directly but by storing the json an querying it, this would allow you to make queries even when the live database isn’t available.

Our next task is to render the declaration of our parameter, return value, or the component columns of a returned table. As this is repeated several times it is worth taking the performance hit and abstracting the logic as a function.

We really want all the information about values in routines to be in a form that is easy to inspect, so as to be able to take it in at a glance. They ought, therefore to be the same as they were in their declaration. This complicates the code more than somewhat, but this justifies having it in a function. Another complication is that we may have documented these values with an extended property. We’d need to pick this information up in an inline comment.

Getting a list of the parameters and their data type for the routine

We now use the function that we just now created, so as to extract all the parameters of the routine whose object_id we pass to it. We need to create this list in the right order.

Getting a list of return values and their data type for the routine

Getting a list of the Columns and their data type for the routine

Several types of routines, such as views and table functions, produce a table source. It is useful to know the columns and their correct order when using them, and it is particularly useful to be able to provide temporary tables with the correct datatypes in order to save data from them

Putting it all together

Now we can put all this together. This can be executed as is, or turned into a stored procedure


Now we’ve got this far, it should give you some ideas for improvement. This rather depends on your team standards for documentation. You can, for example, document the structured headers that you put into your code. This means updating whatever structured header there is with the current version whenever the code is updated. It is quite easy to do this.

It should be possible to edit a JSON document like this to add a description of each routine, column and parameter. This could then be processed by a routine that saved it to the database as extended properties. As you know, these are surprisingly difficult to code by hand.

I hope this gets you thinking about ideas for improving database documentation to assist teamwork. I’m as bad as anyone in documenting my code and for some reason, every fibre of my body tends to resist adding explanation for code. For this reason, I cherish every device for making the documentation chore easier. This is one of them!