Find something in a Stored Procedure

Forgive me if this is something old (like using a…well using statement in SQL). I remember a time when you had to download some obscure freeware application to scan through all your stored procedures to find references to some text or table.

Years after I was faced with just this challenge, but pleasantly surprised on how easy it is to find. Perhaps it was always there and just not available BG (that’s Before Google for the non-believers).

But I digress; simple SQL statement:

select *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),’IsMSShipped’) =0
and OBJECT_DEFINITION(OBJECT_ID(SPECIFIC_NAME)) like ‘%YOUR SEARCH TEXT HERE%’
AND ROUTINE_TYPE=’PROCEDURE’