Simple 5 minute tip here, but I’d done this ages ago and forgotten how to do it.
Suppose you know somewhere in the multitude of stored procedures in the database you’ve inherited that there’s likely a reference to a field or table, and you don’t know where it is. How do you find it without going through each stored procedure and checking it?
Easy. The definitions are stored in the system tables, just like pretty much all structure of a MSSQL database, so we just take a look in those tables, or the INFORMATION_SCHEMA views.
There’s a few different ways of getting at the information, but let’s say you’re looking for references to the field email, and the table Organisation:
SELECT routine_name, created, last_altered, routine_definition FROM information_schema.routines WHERE routine_definition LIKE '%email%' AND routine_definition LIKE '%Organisation%' SELECT NAME, create_date, modify_date, OBJECT_DEFINITION(OBJECT_ID) as Routine_Definition FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%email%' AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%Organisation%'
or all references to a different email field that include no reference to the Organisation table? This one’s a bit more likely give false negatives – e.g. the procedure may reference the Organisation table in some way not related to the email field. But you get the idea.
SELECT routine_name, created, last_altered, routine_definition FROM information_schema.routines WHERE routine_definition LIKE '%email%' AND routine_definition NOT LIKE '%Organisation%'
