I have a database with hundreds of queries created by many people over the years. Now I can investigate for each query if any SSRS report, application for the database or other queries are using each query manually, but as everyone can imagine this is penal labor...

So I found some articles in the web about unused tables or procedures. So as list all queries with

SELECT * FROM sys.all_objects WHERE type = 'V' AND is_ms_shipped = 0

Can I somehow find out if this query is ever been called in a period of time?

E.g. since start which I can get via

SELECT sqlserver_start_time  AS LastSQLServiceRestart FROM sys.dm_os_sys_info

then I would take this date and list all views that aren't used in this time until now.


Community wiki answer:

In general, nothing exists built in that automagically keeps count like this. Some people tend to use indexes on a table as last access but that's sketchy at best.

The honestly best thing you can do is create an audit, trace, or extended event session and garner the object usage from that. This will also require extra work on things such as stored procedures where the procedure is executed and then may touch multiple or no tables, views, etc., based on logical flow.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.