| Author |
Determine if database objects are being accessed
|
|
|
| Is there anyway of determining if a view or function is being accessed?
I've moved a lot of objects between schemas and to prevent any code
breaking used views and functions to 'redirect' to the new schema.
Is there any way to determine if anything is accessing these objects so
I know I'm safe to remove them? The profiler doesn't seem to be
able to do it. I'm using SQL Server 2005.
| |
| John Bell 2006-10-25, 6:00 am |
| Hi
AFAIK there is not any very simple method of doing this, that can be 100%
guaranteed.
If you have scripted all your stored procedures etc. then you could use a
textual search to find references to these views. As you are already using
functions then you could add auditing to them.
John
"Mives" wrote:
> Is there anyway of determining if a view or function is being accessed?
> I've moved a lot of objects between schemas and to prevent any code
> breaking used views and functions to 'redirect' to the new schema.
> Is there any way to determine if anything is accessing these objects so
> I know I'm safe to remove them? The profiler doesn't seem to be
> able to do it. I'm using SQL Server 2005.
>
>
| |
|
| I think that those queries might do the job:
--check stored procedures
select specific_name
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%ObjectName%'
--check views
select TABLE_NAME
from INFORMATION_SCHEMA.VIEWS
where VIEW_DEFINITION like '%ObjectName%'
The queries will help only for objects in the database, but if you
have an application that uses direct SQL statements, you'll get runtime
errors. You can try and use the profiler to see if external
applications use the old names in direct statements. You can also try
to create a synonym with the old name that will point to the new name
(didn't try it myself, but I think that this should work)
Adi
| |
| tootsuite@gmail.com 2006-10-25, 6:00 am |
| A clunky, but effective way to track if someone is using the view or
trigger is:
1.) VIEW - add a trigger on the view to write to a table everytime the
view is accessed
2.) TRIGGER - have the trigger write to a table everytime the trigger
fires, as part of the trigger
You can include timestamp and system_user data in your table
Adi wrote:
> I think that those queries might do the job:
>
> --check stored procedures
> select specific_name
> from INFORMATION_SCHEMA.ROUTINES
> where ROUTINE_DEFINITION like '%ObjectName%'
>
> --check views
> select TABLE_NAME
> from INFORMATION_SCHEMA.VIEWS
> where VIEW_DEFINITION like '%ObjectName%'
>
> The queries will help only for objects in the database, but if you
> have an application that uses direct SQL statements, you'll get runtime
> errors. You can try and use the profiler to see if external
> applications use the old names in direct statements. You can also try
> to create a synonym with the old name that will point to the new name
> (didn't try it myself, but I think that this should work)
>
> Adi
| |
| tootsuite@gmail.com 2006-10-25, 6:00 am |
| oops
2.) FUNCTION - have the function write to a table as part of the
function
tootsu...@gmail.com wrote:[color=darkred
]
> A clunky, but effective way to track if someone is using the view or
> trigger is:
>
> 1.) VIEW - add a trigger on the view to write to a table everytime the
> view is accessed
>
> 2.) TRIGGER - have the trigger write to a table everytime the trigger
> fires, as part of the trigger
>
> You can include timestamp and system_user data in your table
>
>
> Adi wrote:
| |
|
| Is it possible to have a Trigger that is fired when a view is read
selected from?
tootsuite@gmail.com wrote:[color=darkred
]
> A clunky, but effective way to track if someone is using the view or
> trigger is:
>
> 1.) VIEW - add a trigger on the view to write to a table everytime the
> view is accessed
>
> 2.) TRIGGER - have the trigger write to a table everytime the trigger
> fires, as part of the trigger
>
> You can include timestamp and system_user data in your table
>
>
> Adi wrote:
| |
| Roy Harvey 2006-10-25, 6:00 am |
| On 23 Oct 2006 09:40:08 -0700, "Mives" <michaelives@gmail.com> wrote:
>Is it possible to have a Trigger that is fired when a view is read
>selected from?
No.
| |
| John Bell 2006-10-25, 6:00 am |
| Hi
If you never update the views change them into functions.
John
"Mives" wrote:
> Is it possible to have a Trigger that is fired when a view is read
> selected from?
>
> tootsuite@gmail.com wrote:
>
>
|
|
|
|