Search through stored procedures in SQL Server for a specific string of characters
While I have experience in designing and programming SQL databases, I don’t often deal with databases or SQL directly at work. Every so often, I find I need a nice query for doing something very specific within the SQL Server environment. Today I needed to find a specific column that was being used in our stored procedures because the table from which the column resided in was being removed from our database. The biggest problem we have (in my opinion) is numerous stored procedures (almost 600) so finding all references to the column by hand is out of the question. Of course, I would never want to manually search for anything to begin with but I digress. I found this nice query that will find any string of characters within your stored procedures.
SELECT OBJECT_NAME(object_id) as sproc, OBJECT_DEFINITION(object_id) as code FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE '%SearchString%' order by sproc asc