Monday, January 10, 2011

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.

Enjoy!!

No comments: