Use sys.sql_modules not INFORMATION_SCHEMA.ROUTINES

Have you ever tried using INFORMATION_SCHEMA.ROUTINES in SQL Server for searching for all procs referencing a specific object? Perhaps something like this;

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%table_name%';

Has this ever bitten you? If not then you’ve been lucky so far. The ROUTINE_DEFINITION column is limited to 4000 characters so you cannot depend on this to return all the appropriate objects. To be sure you are correctly searching your system use the sys.sql_modules view.

SELECT OBJECT_SCHEMA_NAME(o.[object_id]) AS [schema_name],
		o.[name] AS [object_name],
		*
FROM sys.sql_modules sm
INNER JOIN sys.objects o
	ON o.[object_id] = sm.[object_id]
WHERE sm.[definition] LIKE '%table_name%'
ORDER BY [schema_name],
		 [object_name];