Query or log all query with invalid object name error

Asked Aug 02 '22 at 15:54

Active Aug 02 '22 at 15:54

Viewed 160 times

I have got an old and big database with a lot of extra stored procedures. I've executed a query to find unused queries.

SELECT 
  O.name,
  PS.last_execution_time
FROM 
  sys.dm_exec_procedure_stats PS 
INNER JOIN sys.objects O 
  ON O.[object_id] = PS.[object_id]

I found about 2000 unused store procedures. Sql server uptime is about one month. I'm not sure all of this sp's are unused. so I want to find a solution to monitor database after drop this store procedures. I want to know is there a way to query all 'Invalid object name' errors that raise after this big change. I'll appreciate your helps.

asked Aug 02 '22 at 15:54

Amirhossein Yari

2,054
3
26
38

1

Use XEvents to monitor for errors with a filter for error number 208 – Charlieface Aug 02 '22 at 23:58
Thank you so much. I found this item in "error_reported" event. Is it correct? – Amirhossein Yari Aug 03 '22 at 13:22
Yes, you probably want to add a filter `WHERE (error_number = 208)` – Charlieface Aug 03 '22 at 13:24

Query or log all query with invalid object name error

0 Answers0