Search for keywords in stored procedures

When I was working on a project I got a requirement to know how certain columns/tables are populated, which sprocs are populating them. Then I googled for a while about it and got the link about information_schema.routines. information_schema.routines is a system view which returns all the sprocs defined in the database.

You can have a look at the below example to know the usage of it. The below query shows the list of sprocs whose definition contains the word "customers". In general terms I am using this query to know what all sprocs are operating on the table "customers"

use northwind
go
select routine_name,routine_definition from information_schema.routines where routine_definition like '%customers%'

This is very useful when there is a huge database with many number of tables and many sprocs.

It was useful to me as our project was very complex and huge.