checking object usage

Hello,
I'm entering an existing project that has hunderds of views.
I was wondering how can I get usage statistics for the views - which
ones are in use, which aren't, when were they in use, etc.
Thanks in advance,
R. GreenHi
sp_depends may tell you some information about where the view is used but it
does not always give you everything. Alternatively if you are using stored
procedures for access and have scripted them into text files you can
manually search for your views in them.
You may be able to get some idea of usage from analysing output from SQL
profiler, if you use stored procedures it will not be direct access i.e. you
will know if procedure X is called then it uses your view.
John
"Ronald Green" <zzzbla@.gmail.com> wrote in message
news:1145515140.295473.276370@.v46g2000cwv.googlegroups.com...
> Hello,
> I'm entering an existing project that has hunderds of views.
> I was wondering how can I get usage statistics for the views - which
> ones are in use, which aren't, when were they in use, etc.
> Thanks in advance,
> R. Green
>|||Hi John,
thanks for your quick reply.
I'm looking for information like how often a view is used / when was it
last used.
I can't run a profiler on this (production) server and there are hardly
any stored procedures used. Most of the views are used within DTS
packages
Thanks in advnace,
R. Green|||It's a good question, and one I've seen a few times recently. There
must be an app that actually profiles the access of objects instead of
the references in stored procedures. Please post if there is, otherwise
I think I'll write one, I mean we've got the execution plans from
profiler traces, so parsing them and building up some stats can't be
"that" big a deal.|||Hi Ronald
If you can't run profiler then it is unlikely that you will be allowed to
run any other tool. Sampling using say DBCC INPUTBUFFER will give you less
comprehensive information and therefore be less reliable. Either method may
miss a reference to a view used in a very rarely run report.
For the changes you intend to make you will need a test system and full
regression test to make sure that removing anything does not break your
system.
John
"Ronald Green" <zzzbla@.gmail.com> wrote in message
news:1145519552.676946.39150@.j33g2000cwa.googlegroups.com...
> Hi John,
> thanks for your quick reply.
> I'm looking for information like how often a view is used / when was it
> last used.
> I can't run a profiler on this (production) server and there are hardly
> any stored procedures used. Most of the views are used within DTS
> packages
> Thanks in advnace,
> R. Green
>|||hey,
so no 'last accessed date' on views, eh? :)|||Ronald Green (zzzbla@.gmail.com) writes:
> hey,
> so no 'last accessed date' on views, eh? :)
Right. No such thing.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx