SQL - to search for specific string used in views or sp

Sometimes after you defined something in your table, e.g. to assigned "Unknown" to a value which is not found from the source. Later, you decide to change "Unknown" to "Undetermined". Then you need to change all places that use that values. Then the following scripts may speed up your modification:


SELECT [Scehma]=schema_name(o.schema_id), o.Name, o.type
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON o.object_id = m.object_id
WHERE m.definition like '%Unknown%' or  m.definition like '%Unassigned%'