Search text in SQL store procedures

Here we show a couple examples that help to search for one specific store procedure, or table in the store procedure.

--
-- Specify the database to be used
 USE my_db

-- Show all the store procedures 
 SELECT *
 FROM sys.procedures

-- Look in all the store procedures the ones that reference the users table
 SELECT *
 FROM sys.procedures
 WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%users%'


-- Look for all the store procedures the ones using the user_id column
 SELECT *
 FROM sys.procedures
 WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%user_id%'

-- In older versions of SQL
    SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%TEXT%' AND 
   ROUTINE_TYPE='PROCEDURE'