|
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
(comments are locked)
|
|
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. Thanks for the information Grant.
Feb 08 '10 at 11:41 AM
Ian Roke
(comments are locked)
|
|
Ian, I'm pretty certain SQL Server doesnt log the execution of a select statement - either to a view or to a table directly. 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 '10 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 '10 at 08:56 AM
Fatherjack ♦♦
(comments are locked)
|
|
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).
(comments are locked)
|
|
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
(comments are locked)
|
