PASS Data Community Summit logo

PASS Data Community Summit 2023

Get all the latest announcements direct to your inbox

Finding Stuff in SQL Server Database DDL

You'd have thought that nothing would be easier than using SQL Server Management Studio (SSMS) for searching through the DDL for both the names and definitions of the structural metadata of your databases, for the occurrence of a particular string of letters. Not so easy, it turns out, though Phil Factor is able to come up with various methods for various purposes.

Have you ever wanted a search button in SQL Server Management Studio (SSMS)?  Of course, there is a ‘find’ menu item (Cntl F)  but it won’t work in the Object Explorer pane of SSMS to find things in your database schema.  You also have  an object Search feature in  SSMS 2008 within the Object Explorer ‘Details’ window, but that only searches the names of objects, not their definitions. It also doesn’t search all objects, not even columns.  Obviously, you can find strings within the current query window, but this is only a small fraction of what you actually need when you’re developing a database. You want to search for all your database objects, their names, their definitions and their comments.

How difficult can it be to search through the code (definition)  as well as the name of a database object? After all, the object explorer must have a great deal of information about the objects in it. The answer, it seems, is ‘very difficult’.

Summary

935-image002.jpg

Why isn’t it there in SSMS?

I don’t know for sure, but I suspect that the reason there is no way of searching  through the code as well as the name of structural metadata by  code in SSMS is that it isn’t straightforward within the architecture that Microsoft has chosen to use, without breaking a few rules.

One problem is that there could be quite a few database objects around, such as the CLR functions, check constraints, defaults, default constraints, foreign keys , scalar functions, CLR scalar functions, CLR table-valued functions, inline table-functions, internal tables, stored procedures, CLR stored-procedures, plan guides, primary keys, rules, replication filters, service queues, CLR DML triggers, table functions , indexes, default constraints, table types ,user tables , unique constraints, views, XML Schema collections and extended stored procedures. Quite a few of these objects will have code in them.

The code ,or definition, of objects will be found in user-defined rules, defaults, unencrypted Transact-SQL stored procedures, user-defined Transact-SQL functions, triggers, computed columns, CHECK constraints, views, or system objects such as a system stored procedure. Even if you can search through all the code, you’d also probably need to look at the extended properties too. However, that’s just scraping the surface in terms of what is there or potentially there. .

Another problem is that SSMS is wedded to SMO, which provides an object-oriented programmatic model in place of the real way that SQL Server stores information about its objects such as tables, procedures and columns, and the hierarchical structure of objects. It is clever, it is useful, but it is dead slow to navigate when you’re looking at a large database, and trying to extract the contents of  routines.

Why do you need to search your structural metadata?

There are a number of reasons why you might want to peruse your DDL. If you are used to glancing at, or clicking away at, the Object Explorer or using SQL Prompt, then you are probably scanning parts of the metadata. Most searching goes on when you are refactoring or maintaining an existing database that was written by someone else. An especially irksome task is renaming a view, table or a column. A dependency tracker will find the dependent objects but will, even if working perfectly, miss anything that is in dynamic code, embedded in strings. You may think that code only lurks in stored procedures or functions. Oh no. What about constraints, computed columns, defaults, rules, triggers or views?  What of code in a CLR that accesses a database table?  Code appears in a lot of places. Even if you are familiar with the database, it is easy, for example, to forget about a trigger on a table, miss-spell a column-name or overlook an index.  For high-speed programming, the ‘point, click-and-curse’ technique isn’t really an option.

How do you do it?

We’ll go through the various alternative approaches to searching for stuff in your database definition.

Get the free tool

If you don’t want to get immersed in SQL code to do this, SQL Search is free and you don’t even have to give anyone your email address to get it. 

935-image004.jpg

This is perfect for the majority of requirements. At the moment, SQL Search is slightly limited in that the current version will search only the main database objects, but promised soon is the ability to search such things as index names and the contents of extended properties. It doesn’t yet entirely replace the requirement for a TSQL-based solution. However, you can see that it does more than the ‘Object-Explorer-Details’ Search of SSMS since it has found the search term in the text or definition of the stored procedure as well as finding it in the name.

Searching the entire build-script

Let’s start with the very simplest, but one of the most reliable methods of searching. You get out your build script. No build script? You generate it. If you like clicking at things, then use SSMS; otherwise use an automated procedure with PowerShell and SMO(remember that you have to regenerate the build script every time someone else makes an alteration). I still use a slightly retro stored procedure with DMO; it works very well for me. Then, you read it into your favorite text editor and use the search facilities in it. This sounds clunky, but if your editor uses RegEx search, then a lot can be done, including the automatic generation of lists. Personally, I am pretty happy with this approach, but it isn’t always convenient.

935-image006.jpg

The use of a query pane with the build script in it is almost as good, but you don’t have the regular expressions, or the performance of a programmers’ text editor. It also doesn’t help with some of the more advanced operations that you might need. I like making lists, with the help of a RegEx string, of the lines, or context, where each match happened. I can then scan them quickly to find a particular occurrence. (see TSQL Regular Expression Workbench  for an introduction to RegEx). With a Grep tool, and SQLCMD, you can get lists of  lines containing your search string.

The toe-in-the-water. The ‘Help’ system-procedures

There are a number of ‘traditional’ approaches to looking at your structural metadata, but they aren’t going to help much. If you just want a list of the most important objects, then just use…

…but you will miss out on columns and indexes, and quite a few of the less important objects as well. There are other similar stored procedures, some of which are listed below, which are fine for getting specific information, but not much use for answering questions like ‘Where, in the database, is a date conversion used with a German (104) date format?’ The famous sp_helptext is fine for getting the text of a particular object but no more than that. There is also…

  • sp_helpconstraint — all constraint types, their user-defined or system-supplied name, the columns on which they have been defined, and the expression that defines the constraint
  • sp_helpdb — Reports information about a specified database
  • sp_helpextendedproc –currently defined extended stored procedures
  • sp_helpfile — the physical names and attributes of files associated with the current database
  • sp_helpfilegroup -lists file groups
  • sp_helpgroup -lists roles
  • sp_helpindex -lists indexes, and the columns on which the index is built
  • sp_helptext -displays the definition of a routine
  • sp_helptrigger list the triggers and their types

Going Standard: Using Information Schema

Edgar Codd’s fourth rule for a relational database management system is that there should be an Active online catalog based on the relational model that is accessible to authorized users by means of their regular query language:  This means that  users must be able to access the data about the database’s structural metadata (catalog) just as easily as they can access data, and using the same query language that they use to access the database’s data.

The Information_Schema views are a standard way of doing this. You can use them  for searching if you don’t want too much. The queries you use for ad-hoc work, such as ‘which index covers this column?’, are best put into templates and dragged/dropped onto your workspace, or held as some form of snippet. Typing them out laboriously was never an option.  You can, of course, use a stored procedure if you want to do a general search, or run a utility that queries via ODBC.

However, before we get too excited about the information Schema views, note that they have one or two big drawbacks. The first is that they only store the first 4000 characters of the definition of an object. If you write long stored procedures, you can move on to a later part of this article, or stick cosily to the idea of using a traditional programmers’ text editor as I’ve already described. The second drawback is that if you are interested in anything out of the ordinary, such as triggers or extended properties, I’m afraid you’ll be disappointed. Information Schema are provided only to be compatible with the standard, it seems.

Here is a procedure that does what it can to search your database, using Information schema.

…Which will give you something like this….

935-resultinfoschema.jpg

Learning patience: Using SMO

The most potentially powerful means of searching your database objects is by use of Server Management Objects (SMO). You can get a huge range of database and server objects with SMO, as long as you don’t mind waiting, and if you like coding in a .NET language. The problems come when you try to tackle a large database. Because you have to walk the hierarchy of SMOs objects, rather than do a set-oriented operation, you are soon in trouble if you have a lot of tables and columns. SMO provides a logical and consistent interface into a database, but it does not represent the reality of the way that this information is actually stored in the database.

SMO comes into its own as a way of quickly finding out how to access a ‘difficult’ database object. If you use SMO, and run Profiler at the same time to see what SQL is being executed, you can quickly achieve SQL  MegaStar status in your development team.

935-image009.jpg

Using the Object Catalog views

I love the Object Catalog views. Even Edgar Codd might have smiled.  I wouldn’t suggest using them ‘raw’, as they are not as  ‘unwrapped’ or denormalised as the  Information Schema views.  Really, the only way to survive is to have all the information tucked into a stored procedure that you’d call up to find the things you wanted. Here is the routine I use to search through as much as possible of the structure of databases. I’ve written it like a Lego tower, with a series of UNION ALLs so as to allow you to break it up and create your own Lego tower.

…which will give you something like…

935-result.jpg

Using System Tables

Now, you’ll see that this will only work with SQL Server 2005 or 2008. This shouldn’t stop you dead in your tracks if you are stuck on SQL Server 2000 or older, because there are still ways to do it. Instead of using the catalog views, you have to use the system tables. The mapping between the System tables and the System catalog views are all  listed for you on Books on Line here. The big problem is that the code of each routine is stored in as many NVARCHAR(4000) chunks as is required to hold the entire definition, which complicates the code.   A solution that I use for searching through the definitions of routines in SQL Server 2000 database is given here http://www.simple-talk.com/community/blogs/philfactor/archive/2006/06/03/854.aspx  but I wouldn’t want to bulk up this article with a SQL Server 2000 version of the FindString code.

In Conclusion

I use all the methods I’ve described for perusing the structures in databases. If I want quick information in the course of writing a database routine, I use SQL Search. When I’m doing some intensive refactoring, I’ll use the full Build script, but always in a Programmers Text Editor rather than SSMS.  (I once hit Execute instead of ‘open file’, and deleted a database).  I use Information Schema Views wherever possible as they are reasonably future-proof and open-standard.  I use  Object Catalog views when I can’t get what I want from Information_Schema views, and use System tables when I’m having to use older versions of SQL Server.

I realise, with a guilty start, that this article has been slightly self-centred in that I’ve talked about my own preferences and work practices. If you use a different approach to searching your database DDL scripts that I haven’t mentioned, then I’d be fascinated to hear about in it a comment on this article.