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.
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
|||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
|||oops
2.) FUNCTION - have the function write to a table as part of the
function
tootsu...@.gmail.com wrote:[vbcol=seagreen]
> 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:[vbcol=seagreen]
> 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:
|||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.
|||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:
>

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.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|||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|||oops
2.) FUNCTION - have the function write to a table as part of the
function
tootsu...@.gmail.com wrote:[vbcol=seagreen]
> 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:[vbcol=seagreen]
> 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:|||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.|||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:
>sql

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.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|||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|||oops
2.) FUNCTION - have the function write to a table as part of the
function
tootsu...@.gmail.com wrote:
> 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|||Is it possible to have a Trigger that is fired when a view is read
selected from?
tootsuite@.gmail.com wrote:
> 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|||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.|||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:
> > 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
>

Design Time XSD Metadata refresh

My SSIS package has 19 XML Source inputs constructed of 4 different (XSD) Schemas. I'm trying to find a convenient way to refresh the underlying metadata at design time so that I don't have to open each XML Source, change the XSD reference, click on the columns display, change the XSD back to the original but updated XSD just to get say a new default column width for string data. Does anyone have a quicker way to force an SSIS package to pick up changes in a referenced XSD?

Unfortunately, there is no an easy way to do this. Maybe the easiest would be to do this programmatically.

See the following thread for more details:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2073404&SiteID=1

Thanks,

Bob