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!!

Comments

Popular posts from this blog

Migrating Legacy Apps to the New SimpleMembership Provider

Get Asp.Net Profile properties from Sql

JavaScript function to automatically add slashes to date