Ugh. Impact analysis is one my least favorite tasks. And despite the fact it’s gotten easier over the years with the addition of being able to check object dependencies natively within SQL Server Management Studio, I still find myself resorting to wildcard searches of object definitions for various reasons. (For example: dependencies on objects accessed via linked server, hardcoded values, etc.). Back in SQL Server 2000, these types of searches were performed against the syscomments table. In SQL Server 2005 and 2008 we have the new view sys.sql_modules to query.  This eliminates the minor risk with syscomments that in a longer definition (over 4,000 characters) a particular  search string could be split across two rows, either preventing it from showing up in a search or requiring some fancy coding (see this forum post on SQL Server Central).

Here’s the query I use to check sql_modules.

-- http://www.sqldbpros.com
SELECT  OBJECTPROPERTYEX(sm.object_id, 'basetype') AS ObjectType
      , OBJECT_SCHEMA_NAME(sm.object_id) AS schemaName
      , OBJECT_NAME(sm.object_id) AS ObjectName
FROM    sys.sql_modules AS sm
WHERE   OBJECTPROPERTY(sm.object_id, 'IsMSShipped') = 0 --exclude system objects
        AND sm.definition LIKE '%employeeID%'

This will return results similar to the following:

Just a reminder: if the string you’re looking for contains an underscore aka “_” don’t forget that SQL Server considers an underscore a wildcard character! You can “escape” this functionality by following the directions in our post Escaping From An Underscore In A SQL Server Wildcard / LIKE Search