SQL Server -- search through stored procedures?

Question

I am working on a SQL Server database that has hundreds of stored procedures. I would like to be able to search through the text of the queries (NOT the results) to find a list of stored procedures that already exist. For example, if I need to create a new stored procedure, I might want to search through all the existing stored procedures that deal with table MyTable to see if a similar (or even identical) stored procedure already exists. Is there a way to search for the string "MyTable" and end up with a list of all the stored procedures that include "MyTable"?

oops! Going against my usual policy, I didn't search for the answer first... figures that _this time_ it exists. Thanks, everyone! — twpc, Jan 04 '11 at 15:34

score 2 · Answer 1 · answered Jan 04 '11 at 18:39

2

You can use the SSMS add-in ObjectFinder to perform a full-text search over Stored Procedures.

answered Jan 04 '11 at 18:39

Tareq

1,397
27
28

score 1 · Answer 2 · answered Jan 04 '11 at 15:16

CREATE PROC [dbo].[Search_Stored_Procedure_Code]
(
    @SearchStr  varchar(100),
    @RowsReturned   int = NULL  OUT
)
AS
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

To search the database for the keyword 'FlowerOrders' and also find the number of hits:

DECLARE @Hits int
EXEC Search_Stored_Procedure_Code 'FlowerOrders', @Hits OUT
SELECT 'Found ' + LTRIM(STR(@Hits)) + ' object(s) containing this keyword' AS Result

Don't use `syscomments` for this task on SQL Server 2005+. It can fail on definitions > 4000 characters — Martin Smith, Jan 04 '11 at 15:16

score 1 · Answer 3 · edited Aug 29 '12 at 19:26

1

Redgate makes a set of tools that includes a search facility. You may find it useful.

edited Aug 29 '12 at 19:26

Ryan Kohn

13,079
14
56
81

answered Jan 04 '11 at 15:17

Paddy

33,309
15
79
114

SQL Server -- search through stored procedures?

3 Answers3