Is there a way to do a textual search through all stored procedures for a table name?
Not 100% reliable (could yield false positives, or miss things due to dynamic SQL etc):

SELECT s.name, p.name, m.definition
  FROM sys.schemas AS s
  INNER JOIN sys.procedures AS p
  ON s.[schema_id] = p.[schema_id]
  INNER JOIN sys.sql_modules AS m
  ON p.[object_id] = m.[object_id]
  WHERE LOWER(m.definition) LIKE N'%update%tablename%';


You can't replace directly (and I think you'd have a real hard time building a catch-all replace expression anyway, since T-SQL is not exactly rigidly structured), but you can script out the stored procedures you found, replace CREATE with ALTER and run that to apply changes.

I have written a much more expansive stored procedure that I use for searching across all objects in all databases, including even jobs, and I explain several caveats and show an even better way than LOWER() to handle case sensitivity (you may want the search to be case sensitive; the above assumes you don't care and want all matches regardless of case):


https://sqlblog.org/2011/10/06/a-handy-search-procedure