How do I find out the last time a view was used?

I would like to be able to produce a list of views in a database and the last time that the view was used so I can sort it by date and create a list of views that haven't been used in six months.

Is this possible? I was able to do this using sys.dm_db_index_usage_stats for tables but as yet not found anything similar for views.

more ▼

asked Feb 08, 2010 at 07:27 AM in Default

Ian Roke gravatar image

Ian Roke
1.7k 30 33 34

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

You can't do this 100% unless you're running a trace on your system 24/7 and keeping the data or using the auditing mechanisms of 2008 (+1 to Hakan). But, if you don't have these things implemented, you can still check to see if an object has been accessed recently. First, determine where the view is used within your procedures, views, and functions. Then, query the cache using sys.dm_exec_query_stats combined with sys.dm_exec_sql_text. You can look for the objects that use your view and you can look for ad hoc SQL that uses your view.

Just remember, this only shows the information that is currently in cache. If a query aged out of cache a couple of milliseconds before you check the data, it won't be there.

more ▼

answered Feb 08, 2010 at 11:12 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

Thanks for the information Grant.
Feb 08, 2010 at 11:41 AM Ian Roke
(comments are locked)
10|1200 characters needed characters left

Ian, I'm pretty certain SQL Server doesnt log the execution of a select statement - either to a view or to a table directly.

more ▼

answered Feb 08, 2010 at 07:46 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

It isn't so much the execution I am interested in more that the view was "touched" at all. If there has been no activity on a view then it would be safe to assume it isn't regularly used. I am just doing a bit of cleansing on the server. Each additional view/table/sproc takes a certain allocation of time to maintain and document.
Feb 08, 2010 at 07:56 AM Ian Roke
Thinking on from @DavidWinbush's answer - you could use Red-Gate's SQLSearch to find references to the objects you want. http://www.red-gate.com/products/SQL_Search/index.htm
Feb 08, 2010 at 08:56 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

I agree with Fatherjack, SQL server 2005 doesn't log that kind of information, but if you upgrade to SQL server 2008 you can acticate "Audit" to log when an object was last accessed. I implemented Audit in SQL server 2008 and collected the information (stored in files that are recycled) to a table. If an object was not "accessed" in 6 month it was considered "obsolete" and renamed in the next release (after a lot of testing of course).

more ▼

answered Feb 08, 2010 at 08:52 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

(comments are locked)
10|1200 characters needed characters left

If the queries are all stored procs and functions you could search the [text] column in syscomments. It won't tell you when the view was last used but if the view doesn't show up in the results the answer is "not for a while". I wrapped this up in a stored proc and blogged about it here: http://sqlblogcasts.com/blogs/davidwimbush/archive/2009/07/13/better-dependency-checking.aspx

more ▼

answered Feb 08, 2010 at 08:52 AM

David Wimbush gravatar image

David Wimbush
5k 29 31 34

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 08, 2010 at 07:27 AM

Seen: 6336 times

Last Updated: Feb 08, 2010 at 07:37 AM