Tag Archives: SQL

Searching for text in MSSQL stored procedures

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%'
Advertisements

Leave a comment

Filed under General