How can I do a search of the text of all Stored Procedures in a SQL database

Question

In Visual Studio 2k8, I press Ctrl-F and then type some text I want to search for in my project. I can quickly search many files at once.

I have a SQL Server with some stored procedures. How can I easily search my stored procedures for arbitrary strings? I know that I can list the text of one stored procedure in SQL Server Studio and do a Ctrl-F search from there, but I want to search them all at once.

Answer 1

Just script all of the procs to a query window. Then ctrl-f to your hearts content.

Answer 2

SQL Search (from Redgate) is your friend. Free download, integrates straight into SSMS :)

Answer 3

Stored proc text is stored in sysobjects,syscomments.

Example

Answer 4

This searches all views, procedures and user defined functions:

CREATE PROCEDURE usp_SearchFor
    @PatternIn AS varchar(max)
   ,@ObjectNamePattern AS varchar(max) = NULL
AS 
BEGIN
    DECLARE @Pattern AS varchar(max)
    SET @Pattern = '%' + @PatternIn + '%' ;
    WITH    ROUTINES
              AS (
                  -- CANNOT use INFORMATION_SCHEMA.ROUTINES because of 4000 character limit
        SELECT  o.type_desc AS ROUTINE_TYPE
                   ,s.[name] AS [SCHEMA_NAME]
                   ,o.[name] AS ROUTINE_NAME
                   ,m.definition AS ROUTINE_DEFINITION
            FROM    sys.sql_modules AS m WITH (NOLOCK)
            INNER JOIN sys.objects AS o WITH (NOLOCK)
                    ON m.[object_id] = o.[OBJECT_ID]
            INNER JOIN sys.schemas AS s WITH (NOLOCK)
                    ON s.[schema_id] = o.[schema_id]
            WHERE   m.definition LIKE @Pattern
                 ),
            Results
              AS (
                  SELECT    ROUTINE_TYPE
                           ,[SCHEMA_NAME]
                           ,ROUTINE_NAME
                           ,PATINDEX(@Pattern, ROUTINE_DEFINITION) AS StartPos
                           ,SUBSTRING(ROUTINE_DEFINITION,
                                      PATINDEX(@Pattern, ROUTINE_DEFINITION),
                                      255) AS WorkItem
                           ,RIGHT(ROUTINE_DEFINITION,
                                  LEN(ROUTINE_DEFINITION) - PATINDEX(@Pattern, ROUTINE_DEFINITION)) AS Remainder
                           ,1 AS Occurrence
                  FROM      ROUTINES
                  UNION ALL
                  SELECT    ROUTINE_TYPE
                           ,[SCHEMA_NAME]
                           ,ROUTINE_NAME
                           ,PATINDEX(@Pattern, Remainder) AS StartPos
                           ,SUBSTRING(Remainder, PATINDEX(@Pattern, Remainder),
                                      255) AS WorkItem
                           ,RIGHT(Remainder,
                                  LEN(Remainder) - PATINDEX(@Pattern,
                                                            Remainder)) AS Remainder
                           ,Occurrence + 1 AS Occurrence
                  FROM      Results
                  WHERE     PATINDEX(@Pattern, Remainder) > 0
                 )
        SELECT  'Search For:' + @PatternIn AS Problem
               ,ROUTINE_TYPE
               ,QUOTENAME([SCHEMA_NAME]) + '.' + QUOTENAME([ROUTINE_NAME]) AS ROUTINE_NAME
               ,WorkItem
        FROM    Results
        WHERE   @ObjectNamePattern IS NULL
                OR ROUTINE_NAME LIKE @ObjectNamePattern
        ORDER BY ROUTINE_TYPE
               ,[SCHEMA_NAME]
               ,ROUTINE_NAME
               ,Occurrence
END

Answer 5

You can use a simple search as below

select ROUTINE_name from INFORMATION_SCHEMA.ROUTINES
where CHARINDEX('demo',routine_definition,1)>0

substitute your arbitrary string for 'demo'

Answer 6

SELECT DISTINCT OBJECT_NAME(ID) FROM SysComments WHERE Text LIKE '%Search%'

Deprecated apparently, but still works in SQL 2008