Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL Server : How to find a stored procedure containing specific text

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 158
    Comment on it

    Many times during SQL server development we need to search for a stored procedure containing a specific text. This helps in checking for dependencies for objects in stored procedures or sometimes we might be simply interested in searching for a hard coded text. We can use the below approaches for finding the same:

     

    1) Using INFORMATION_SCHEMA.ROUTINES

        SELECT ROUTINE_NAME, ROUTINE_DEFINITION
        FROM INFORMATION_SCHEMA.ROUTINES 
        WHERE ROUTINE_DEFINITION LIKE '%SearchText%' AND ROUTINE_TYPE='PROCEDURE'

    2) Using SYSCOMMENTS

        SELECT OBJECT_NAME(id) 
        FROM SYSCOMMENTS 
        WHERE [text] LIKE '%SearchText%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
        GROUP BY OBJECT_NAME(id)

    3) Using SYS.SQL_MODULES

        SELECT OBJECT_NAME(object_id)
        FROM SYS.SQL_MODULES
        WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1 AND definition LIKE '%SearchText%'

     

    In the below query the three approaches are combined into one single query :

    SELECT DISTINCT [StoredProcedureName] 
    FROM (
        (SELECT ROUTINE_NAME [StoredProcedureName]
            FROM INFORMATION_SCHEMA.ROUTINES 
            WHERE ROUTINE_DEFINITION LIKE '%' + @Search + '%' 
            AND ROUTINE_TYPE='PROCEDURE')
        UNION ALL
        (SELECT OBJECT_NAME(id) [StoredProcedureName]
            FROM SYSCOMMENTS 
            WHERE [text] LIKE '%' + @Search + '%' 
            AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
            GROUP BY OBJECT_NAME(id))
        UNION ALL
        (SELECT OBJECT_NAME(object_id) [StoredProcedureName]
            FROM sys.sql_modules
            WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
            AND definition LIKE '%' + @Search + '%')
    ) AS T
    ORDER BY T.[StoredProcedureName]

     

    Hope the above article helps you in solving a very common but equally important problem we encounter during SQL server development.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: