Is there a way to do a textual search through all stored procedures for a table name? I've just inherited a database with a number of tables and stored procedures.  I use Microsoft SQL Server Management Studio.

One, or possibly more, stored procedures update a specific table.  I'd like to do a textual search through all the stored procedures to look for the procedure that updates the table.

If the raw SQL was in our code, I'd just grep the code base, but with stored procedures I'm not sure how to search through all the stored procedures.

I'd also like to rename a few things and do a find/replace throughout the stored procedures.

Is it possible to search and replace in stored procedures using Microsoft SQL Server Management Studio?
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