Search Stored Procedures with SysComments - The Inner Workings of Your Database Revealed!

Hello and welcome back to SQL Coach!!

Two days ago I shared some information with you about the SysObjects table. Tonight I want to talk about SysComments.

In case you missed the Tuesday evening post, SysComments is one of SQL Server's System Tables. Do not attempt to DELETE, INSERT or UPDATE the table in this table. That is SQL Server's job and we need to leave it up to SQL Server to decide what to update and when.

Have you ever been in the situation where you needed to create a stored procedure to accomplish something that you know you have already written or at least saw a stored procedure that performed a task similiar to what you need to accomplish?

And have you ever been frustrated because no matter how hard you tried to remember you simply could not recall which stored procedure that code is in?

If you only have a handful of simple stored procedures it would be a simple task to open each and perform a search for some part of the code that you remember.

But, wouldn't it be so much simpler if you could simply write a query and have SQL Server do the work for you and return a listing of any stored procedures that are good candidates?

I am about to show you how to accomplish this... are you ready? Great, let's get started!

Remember that SysComments stores the actual text (code) for your stored procedures and functions. The table also contains an ID field that maps back to the id field in SysObjects.

The column in SysComments that stores the text of your stored procedures and functions is called simply enough text.

Here is a very simple example of how to search for all stored procedures that contain an Insert operation:

SELECT so.name
FROM dbo.sysobjects so
INNER JOIN dbo.SysComments sc
ON sc.id = so.id
WHERE so.xtype = 'P'
AND sc.text LIKE '%INSERT%'


Now, one thing you may notice is if you have a stored procedure with a lot of text and several INSERT statements spread down throughout the code the above query will return that stored procedure's name more than once.

The reason for this is the text column in the SysComments table can only hold 8000 characters. If the text in the procedure is longer than 8000 characters SQL Server needs to create additional rows. In each new row, the text column will contain up 8,000 characters (or however many characters are left) of text until finally all text in the procedure is accounted for.

No worries though, there is a simple solution to the names being returned multiple times, simply add a DISTINCT to your SELECT statement so it looks like this:

SELECT DISTINCT so.name
FROM dbo.sysobjects so
INNER JOIN dbo.SysComments sc
ON sc.id = so.id
WHERE so.xtype = 'P'
AND sc.text LIKE '%INSERT%'


Now, you have the ability to search the text (code and comments) of your stored procedures. And, believe me, this can be a real timesaver when your database grows to more than a dozen or so of long procedures. Beyond the time savings searching like is fun. Make SQL Server do the work for you!

Let me share one more trick with you before I end this post.

You do not even need to link back to the SysObjects table as long as you do not care what type of objects are returned by your search.

For example, if you want all stored procedures, scalar functions, etc returned that contain the word "password" you can accomplish this with the following statement:

SELECT DISTINCT OBJECT_NAME(id)
FROM dbo.SysComments
WHERE text LIKE '%password%'

Here is how it works: SQL Server provides the OBJECT_NAME function. It accepts an ID value (from the sysobjects table) and returns the name. The SysComments table contains an ID column that links back to the ID column in the SysObjects table.

Okay, that's it for this post. Remember to come back often and bring your friends!