We currently have many views. Some created long before my time. Is the a way to determine which views are being used actively and which once are obsolete?
asked Mar 06 '12 at 07:48 AM in Default
Unfortunately, there is no certain way to find such information. There are some workarounds but they have so many consequences and dependencies. For e.g. Below script would give you the name of all of your VIEWS and the last execution time
But the caveat is the cache as the lifetime of the output field
The next options are run a trace for over a certain duration for e.g. over a month. Not to forget month ends, quarter ends, half year and so on.. according to your environment.
But at the end it all would be a laborious work and It cannot be sorted out in one go.
This is pretty tricky to track down, you can query the executing queries dmvs with something like
but this could be a bad query to run on a busy production server, it will take a lot of effort to complete.
You could use extended events to catch executing code too but that again has performance considerations.
You may be best simply reviewing the system with the system administrator and carefully renaming views and so on until you identify those that are in use.
answered Mar 06 '12 at 08:49 AM
Thank you for responding. We have many reports and our LIMS application runs of these views.
When a view is called via a report will the execution time also be updated?
We have been considering going the trace route as you suggested but I was hoping that we would find a magical solution here. :-)
answered Mar 07 '12 at 11:30 AM