Suppose you have deleted one of the column of any table and now you have to change all your procedures where this column is used.
1. One way is to open every procedure and look for the table and the column name.
2. Second solution is the
Select * from information_Schema.Routines where Routine_Definition like '%table1%'
This query will list out all the procedures in which table name specified in where conditoin is used.
INFORMATION_SCHEMA.ROUTINES view is used to retrieve information about stored procedures. This view contains one row for each stored procedure accessible to the current user in the current database.
The INFORMATION_SCHEMA.ROUTINES view was introduced in SQL Server 2000. This view is based on the sysobjects, syscomments and other system tables.