Prev: Incorrect Result
Next: How to pivot this
From: Brian on 25 May 2010 01:25


Hi we have discovered a view that will cause a problem when we next upgrade
our database. I need to find a way to tell if it is being used. I have
checked dependencies and there is none but that won't tell me if the
application is using it or not.

With stored procedures, if I want to know if they are being used I add code
to write to a table whenever they are run. With tables I write a trigger that
writes to a table whenever the table is being changed. But I cannot do either
with views as you can only do a select and I want to know if it is being run
not necessarily looking for tables updated.

Is there a way to tell if a view is being used?

Thanks in advance Brian
From: Geoff Schaller on 25 May 2010 05:57
In short, no.

Firstly you will never detect dynamic sql and secondly, unless you scour
the code of all applications that use the database, you'll never know
what might use this view.

It's a fact of life.
All you can do is mitigate the problem by not deleting it.


Sorry, Geoff




"Brian" <Brian(a)discussions.microsoft.com> wrote in message
news:A6F7E7B4-7928-43DB-B397-5E12A94AC234(a)microsoft.com:

> Hi we have discovered a view that will cause a problem when we next upgrade
> our database. I need to find a way to tell if it is being used. I have
> checked dependencies and there is none but that won't tell me if the
> application is using it or not.
>
> With stored procedures, if I want to know if they are being used I add code
> to write to a table whenever they are run. With tables I write a trigger that
> writes to a table whenever the table is being changed. But I cannot do either
> with views as you can only do a select and I want to know if it is being run
> not necessarily looking for tables updated.
>
> Is there a way to tell if a view is being used?
>
> Thanks in advance Brian

From: John Bell on 25 May 2010 06:01
On Mon, 24 May 2010 22:25:01 -0700, Brian
<Brian(a)discussions.microsoft.com> wrote:

>
>
>Hi we have discovered a view that will cause a problem when we next upgrade
>our database. I need to find a way to tell if it is being used. I have
>checked dependencies and there is none but that won�t tell me if the
>application is using it or not.
>
>With stored procedures, if I want to know if they are being used I add code
>to write to a table whenever they are run. With tables I write a trigger that
>writes to a table whenever the table is being changed. But I cannot do either
>with views as you can only do a select and I want to know if it is being run
>not necessarily looking for tables updated.
>
>Is there a way to tell if a view is being used?
>
>Thanks in advance Brian


Hi Brian

You don't give which version of SQL server you are on! I don't think
there is a way of being 100% certain that you capture all usage unless
access has been limited to stored procedures and function, which you
can script and search them. Similarly it may be possible to search
application code.

You could try and detect other use by using profiler and searching the
textdata, but this is likely to create some large files so you have to
be careful and may not catch all occurences of the usage.

John
From: Gert-Jan Strik on 25 May 2010 12:43
As John mentioned, you can use SQL Profiler to monitor all statements.
You can filter on the name of the view in the "text" column. Depending
on what you monitor SQL Profiler has more or less impact on performance.
In other words: it can use a little or quite a significant amount of
CPU.

The advantage of it is that you get to see which application launched a
statement with the view name in it, and you can also see the query.

--
Gert-Jan


Brian wrote:
>
> Hi we have discovered a view that will cause a problem when we next upgrade
> our database. I need to find a way to tell if it is being used. I have
> checked dependencies and there is none but that won't tell me if the
> application is using it or not.
>
> With stored procedures, if I want to know if they are being used I add code
> to write to a table whenever they are run. With tables I write a trigger that
> writes to a table whenever the table is being changed. But I cannot do either
> with views as you can only do a select and I want to know if it is being run
> not necessarily looking for tables updated.
>
> Is there a way to tell if a view is being used?
>
> Thanks in advance Brian
 | 
Pages: 1
Prev: Incorrect Result
Next: How to pivot this