SQL Server Grep Function for Keyword Code Searching

Back in the days when I was working with Sybase there was a function I found and used often to search for keywords in database code areas called sp_grep. You could pass a keyword to the stored procedure and it would return all objects that contained code with the keyword present.

This week I had the need to run Grep on a SQL Server database that we have taken over the maintenance of. I found this very useful stored procedure called sp_search_code by Narayana Vyas Kondreddi that performed the needed search.

USE [SOMEDB]
GO
/****** Object:  StoredProcedure [dbo].[sp_search_code]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_search_code]
(
@SearchStr     varchar(100),
@RowsReturned    int = NULL    OUT
)
AS
/**********************************************************************************
        Copyright © 1997 - 2002 Narayana Vyas Kondreddi. All rights reserved.
                                         
Purpose:    To search the stored proceudre, UDF, trigger code for a given keyword.

Written by:    Narayana Vyas Kondreddi
        http://vyaskn.tripod.com

Tested on:     SQL Server 7.0, SQL Server 2000

Date created:    January-22-2002 21:37 GMT

Date modified:    February-17-2002 19:31 GMT

Email:         [email protected]

Examples:

To search your database code for the keyword 'unauthorized':
EXEC sp_search_code 'unauthorized'

To search your database code for the keyword 'FlowerOrders' and also find out the number of hits:
DECLARE @Hits int
EXEC sp_search_code 'FlowerOrders', @Hits OUT
SELECT 'Found ' + LTRIM(STR(@Hits)) + ' object(s) containing this keyword' AS Result
*************************************************************************************************/
BEGIN
    SET NOCOUNT ON

    SELECT    DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name',
        CASE
            WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1
                THEN 'Replication stored procedure'
            WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
                THEN 'Extended stored procedure'               
            WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1
                THEN 'Stored Procedure'
            WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1
                THEN 'Trigger'
            WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
                THEN 'Table-valued function'
            WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
                THEN 'Scalar-valued function'
            WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
                THEN 'Inline function'   
        END AS 'Object type',
        'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) + '''' 
        AS 'Run this command to see the object text'
    FROM    syscomments c
        INNER JOIN
        sysobjects o
        ON c.id = o.id
    WHERE    c.text LIKE '%' + @SearchStr + '%'    AND
        encrypted = 0                AND
        (
        OBJECTPROPERTY(c.id, 'IsReplProc') = 1        OR
        OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1    OR
        OBJECTPROPERTY(c.id, 'IsProcedure') = 1        OR
        OBJECTPROPERTY(c.id, 'IsTrigger') = 1        OR
        OBJECTPROPERTY(c.id, 'IsTableFunction') = 1    OR
        OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1    OR
        OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1   
        )

    ORDER BY    'Object type', 'Object name'

    SET @RowsReturned = @@ROWCOUNT
END