Audit VIEWS being used. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Audit VIEWS being used.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-06-29 : 08:11:11

Ahmed Mohamed writes "Hi,
I am busy Auditing our SQL DB and am having problems auditing the VIEWS.

Basically, we want to check what views are being used and what views are not being used to that we can remove them (archive) them on a backup server.

I've done the following with the SP's, i added this code:

declare @@spNAME as varchar(25)

set @@spNAME = (select sysobjects.name from sysobjects where sysobjects.xtype = 'P' and sysobjects.id = @@PROCID)

insert into agObjectAudit (objectName,dateLastRun)
values ( @@spNAME ,getDate())

it inserts the current SP name and date when it is run. I cannot do this with a VIEW and tried using an instead of trigger to no avail. it could be that i was using the trigger wrong.

here is my trigger:

CREATE TRIGGER dbo.t_VIEW1 ON VIEW1
INSTEAD OF INSERT AS
BEGIN
insert into agObjectAuditTMP (objectName, dateLastRun)
values ( 'VIEW1' ,getDate())
END

I've been trying now for 3 days straight with no progress.

please help me.

is there a table that records when an object is fired??? the sysobjects table only stores the date created.

Regards
Ahmed Mohamed"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-29 : 14:31:09

You'll need to run SQL Profiler to audit usage.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources