How to make your code searchable?
Last updated: February 9th '02 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

First of all, why would one want to search the code of stored procedures, triggers or user defined functions? Here are some simple reasons:

• During the development stage, for some reason, you had to change the name of a table's column. You already have a bunch of stored procedures that are referencing this column. Now, you need to identify all those stored procedures and replace the old column name with the new one.
  
  
• You deployed your database application and one of your clients calls you back with an error number that he had received while running a query. Now you need to find out which stored procedure raised that error message.

There are a lot of other situations that require you to search your database code. So, how would a DBA or a developer go about searching database code? Here are a couple of options that I can think of:

• Create a stored procedure that searches the 'text' column of syscomments table. Syscomments table stores the text of stored procedures, triggers, views, user defined functions etc. I created a stored procedure that does just this. Create this stored procedure in the master database and call it from the database of your choice, just as would call a local stored procedure. You will find this procedure a little slow if you have a lot of stored procedures with bulky code, as it performs a table scan on syscomments table. You will not be able to search on keywords that are split across rows in syscomments table.
  
  

  	Click here to download the procedure sp_search_code
  	Note: The second column of the output shows the object type and the third column gives you the command needed to view the complete text of that object. Feel free to alter the search condition in this stored procedure, to suit your searching needs.

• Save all your database scripts on disk and make use of Windows Indexing features to search for key words. The disadvantage with that method is that, you may have to duplicate your code on disk, if you are already storing your code in a version control software like Visual Source Safe (VSS).
  
  
• Extract the contents of syscomments table into your own table and create a Full-Text catalog on this table. Now you can search your database code using the rich search capabilities of Full-Text search feature (SQL Server 7.0 and above). Disadvantage with this method is that, you must keep updating the Full-Text catalogs manually, as and when your stored procedure code changes.