{"id":795,"date":"2010-02-04T00:00:00","date_gmt":"2010-02-04T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/finding-stuff-in-sql-server-database-ddl\/"},"modified":"2021-09-29T16:22:01","modified_gmt":"2021-09-29T16:22:01","slug":"finding-stuff-in-sql-server-database-ddl","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/finding-stuff-in-sql-server-database-ddl\/","title":{"rendered":"Finding Stuff in SQL Server Database DDL"},"content":{"rendered":"
\n

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.<\/p>\n

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’.<\/p>\n

Summary<\/h1>\n

\"935-image002.jpg\"<\/p>\n

Why isn’t it there in SSMS?<\/h1>\n

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.<\/p>\n

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.<\/p>\n

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. .<\/p>\n

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.<\/p>\n

Why do you need to search your structural metadata?<\/h1>\n

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.<\/p>\n

How do you do it?<\/h1>\n

We’ll go through the various alternative approaches to searching for stuff in your database definition.<\/p>\n

Get the free tool<\/h2>\n

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

\"935-image004.jpg\"<\/p>\n

This is perfect for the majority of requirements. At the moment, SQL Search<\/a> 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.<\/p>\n

Searching the entire build-script<\/h2>\n

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<\/a>; 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.<\/p>\n

\"935-image006.jpg\"<\/p>\n

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<\/a>  for an introduction to RegEx). With a Grep<\/b> tool, and SQLCMD,<\/b> you can get lists of  lines containing your search string.<\/p>\n

The toe-in-the-water. The ‘Help’ system-procedures<\/h2>\n

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… <\/p>\n

Execute sp_help --list all objects in the sys.sysobjects table<\/pre>\n
…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<\/strong> is fine for getting the text of a particular object but no more than that. There is also…<\/p>\n