SQL Server Script : Search content or text within SQL Stored Procedures in database using SQL Query

There are many situations where in past we did some useful functionality in some stored procedures after that as time goes we forgot that how exactly we had done that functionality and now we want implement that functionality somewhere else and we do not remember where we used, but we know some important keyword or string that we used at that place. At that time this query is useful.

This query searches in all Stored Procedures in database according to given search criteria and give use the search results. In this query we are using sys.sql_modules and sys.objects system tables.

SQL Query : 

SELECT 
        so.[name] AS SP_NAME ,
        so.type_desc AS ROUTINE_TYPE,
        sm.definition AS SP_DEFINITION
FROM sys.sql_modules AS sm
INNER JOIN sys.objects AS so
    ON sm.object_id = so.object_id
WHERE sm.definition LIKE '%Quantity%'
and type='P'

Output :

(To view original image , click on image)

This is the very useful SQL Server Scripts.

Note : Give us your valuable feedback in comments. Give your suggestions in this article so we can update our articles accordingly that.