SQL To Find String In Procedure/Function
I do a lot of work updating legacy systems. Almost every system needs the column names updated since the names are usually short and not very descriptive. Here’s a command for finding all store procedures/functions that use a specified column.
select s.name, a.name FROM sys.sql_modules m inner join sys.all_objects a on m.object_id = a.object_id inner join sys.schemas s on a.schema_id = s.schema_id where m.definition like '%MyColumnName%'
INFORMATION_SCHEMA.ROUTINES is unreliable because it is limited to the first 4000 characters.