SQL Server Finding Stored Procedures Using Specific Objects

My last few posts have focused on exploiting the DMV sys.dm_db_index_usage_stats, in particular to find unused indexes. The next natural step was to find objects that we thought were retired but still being used. This is a common occurrence in highly changing code sites. This is a surprisingly simple script, using sys.dm_exec_sql_text. The reason I use this over syscomments, or sp_helptext, or sys.sql_modules, is that this gets the info from the proc cache and hence has a history of when it was last used. The other views only give you definition info, the SP referencing it might be out dated itself. Also using this view will allow you to capture ad-hoc SQL, although my view (No Pun intended) is any ad-hoc sql deserves to break anyway.

select creation_time, last_execution_time, Object_name(Objectid), text from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle)

where text like '%Table1%'