Finding code in Object Definitions

INFORMATION_SCHEMA Views

Today I was trying to find a quick way of finding all stored procs that had deleted information from a table

So realising the information_schema.routines table held the actual definition for the stored proc, you can actually query the definition to find the code you are looking for.

SELECT *

FROM INFORMATION_SCHEMA.routines

WHERE ROUTINE_DEFINITION LIKE '%DELETE FROM ExampleTable%'

OBJECT_DEFINITION

Perhaps the best way is using the sql function OBJECT_DEFINITION. This will take in the sql server object id and return the definition for that object.

Example:

-- OBJECT_DEFINITION function

SELECT OBJECT_DEFINITION(OBJECT_ID('trigger_ExampleTable'))

I have found this is useful for finding code that is in triggers

SELECT NAME,OBJECT_DEFINITION(object_id) 
FROM sys.objects
WHERE TYPE = 'TR' -- triggers