Know Dot Net Article

Searching through your Sql Statements

by William Ryan

If you use Sql Server 2000 or Sql Server 2005, chances are that you will want/need to be able to search through your stored procedures and find instances of some text. So if you are using Visual Studio 2005 Team Edition for Database Professionals (the subject of my next upcoming article series) then life is grand b/c it provides a tremendous amount of find/replace and refactoring capabilities.  If you aren't using Team Edition though, you're choices are a little less abundant.

One way you might go about achieving your goals is to script out your objects and use Sql Server Management Studio or your favorite text editor. If you have a large database though, chances are this will be a pretty large text file which will take a few seconds to generate. You'll also have to make sure you have the latest version of the script each time you search.  For quick and dirty searches, this is a bit much.

Similarly, you can script out each object individually in a file and use Windows search or the Regular Expression library. This however is essentially the same methodology as the first approach so doesn't provide any additional benefit.

Another choice is to look through the System tables and look through Syscomments.  My homie Chris Winland wrote a quick stored procedure to handle this:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GrepUsage]
  @p_SearchText NVARCHAR(1000)
AS
  SELECT OBJECT_NAME(com.[id]) AS [Object Name],
         [Type] = CASE [xtype]
                  WHEN 'P' THEN
                    'Procedure'
                  WHEN 'V' THEN
                    'View'
                  WHEN 'FN' THEN
                    'Function'
                  WHEN 'U' THEN
                    'Table'
                  END,
         SUBSTRING([text], PATINDEX('%' + @p_SearchText + '%', [text]) - 15, 100) AS [First Occurance]
    FROM syscomments com (NOLOCK)
    JOIN sysobjects obj (NOLOCK)
      ON com.[id] = obj.[id]
  WHERE [text] like '%' + @p_SearchText + '%'
    AND [xtype] in ('P', 'V', 'FN', 'U') -- Proc, View, Function

He's created many different versions of this procedure and you can do the same by modifying the WHERE condition.

Using Sql Server 2005 and the INFORMATION_SCHEMA , I created a slightly different version.  The following code searches through the names of each procedure and returns a list of each that matches your criteria:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GREP_Name]
  @ProcedureName VARCHAR
AS
SELECT ROUTINE_NAME
  FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE'
   AND ROUTINE_NAME LIKE '%' + @ProcedureName + '%'

This can be easily expanded to search additional object types or different search criteria.  Stated simply, this procedure looks through each procedure and restricts on matches of the Routine_Name column.

Another version of it searches through the Text of each procedure.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GREP_Definition]
  @ProcedureText VARCHAR
AS
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
  FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE'
   AND ROUTINE_DEFINITION LIKE '%' + @ProcedureText + '%'

To use this approach, you simply restrict on the Routine_Definition column instead of the Routine_Name.  Now in practice, you'd probably want to combine these two procedures and use a switch or two to facilitate Name AND Description matches Similarly, you could plug in some switches to find other object types.

Using queries like this gets the job done and does it quite quickly.  My Next article builds upon this to walk through more and more advanced searching.  Stay tuned...