Identify Dependent Objects with sp_depends

sp_depends lists all the stored procedures that reference the object you specify or all the procedures that it is dependent upon.

For example, this command lists all the objects referenced by the user-created stored procedure byroyalty:

sp_depends byroyalty 
Things the object references in the current database. 
object           type       updated      selected 
---------------- ----------- ---------   -------- 
dbo.titleauthor  user table  no          no
(return status = 0)

The following statement uses sp_depends to list all the objects that reference the table titleauthor:

sp_depends titleauthor 
Things inside the current database that reference the object. 
object          type 
--------------  ------------------ 
dbo.byroyalty   stored procedure
dbo.titleview   view 
(return status = 0)

Dependent objects that reference all columns in the table. Use sp_depends on each column to get more information. Columns referenced in stored procedures views, or triggers are not included in this report. 
(1 row affected)
(return status = 0)

You must drop and re-create the procedure if any of its referenced objects have been renamed.