Find any Text in any Stored Procedure

Paopatsin/ April 2, 2021/ Database, MS SQL, Technology

Please read our Disclaimer

Works With
-Microsoft SQL Server 2016 (RTM) – 13.0.1601.5 (X64)
-Microsoft SQL Server 2014 (SP3-GDR) (KB4532095) – 12.0.6118.4 (X64)

In case you need to find a certain text, a word or a string in your stored procedure assemblage, the following command will come in handy.

Command:

SELECT routine_name, routine_definition FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_definition LIKE ‘%<replace with your text>%’ AND routine_type = ‘PROCEDURE’

Steps and Sample Usage:

In this example, we are going to use Microsoft SQL AdventureWorks2016 sample database.

1. Open and log into your SQL Management Studio
2. Open a New Query Windows or (CTRL+N)
3. Run the following commands: (Copy and Paste the code block to your Query Window)

–Let’s search for the word ‘assembly‘
SELECT routine_name, routine_definition
FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_definition LIKE ‘%assembly%’
AND routine_type = ‘PROCEDURE’

4. Execute the query by pressing F5, you should see something similar below.

Use Case:

1. You need to find a stored procedure/s that might be relevant to your keyword.
2. You can use as a tool to quickly look for dependencies between stored procedures.

Caveat:

1. There is a chance of a hit and miss because of non-standardized stored procedure definition.

Sample Data:
-AdventureWorks2016 Database