Search All Stored Procedures That Contain Specific Text

You have changed the column name from titleauthor to t_author in your table and you have 200 stored procedures
The problem is that you don't remember which of these 200 procedures use that column
You run sp_depends 'authors' but like we all know this procedure is not always accurate
So instead of running sp_depends we can take a look at the INFORMATION_SCHEMA.ROUTINES ANSI SQL Schema view

Let's search for all procedures that have the text titleauthor in them

USE pubs
GO

SELECT SPECIFIC_NAME,*
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE ='PROCEDURE'
AND ROUTINE_DEFINITION LIKE '%titleauthor%'