Recently I had a problem that I needed to update my stored procedures to point at a new set of tables. I systematically worked through my 3 stored procedures for populating the tables, but I couldn't remember all the stored procedures that accessed them for data retrieval.
Enter information_schema.routines and a tip from Experts Exchange:
select * from information_schema.routines where routine_definition like '%yourtablename%'
The downside is that this searches the stored procedure text (well, the first 4000 characters in the routine_definition column) for the table name, so you'll get hits even where the table name is used in a comment. The upside is that dynamic SQL is accounted for.
posted @ Tuesday, June 15, 2004 11:29 AM