How to tell if a table is being used.

Currently I've been assigned the glorious task of cleaning up our database. I've gone ahead and marked a sizable list of tables that I don't believe are accessed anymore. Now, I need to find out whether or not they are being accessed. I've ran sp_depends on all of them to find out what stored procedures/triggers may be accessing them, and the plate is clear. But, there are many individual programs, reports, etc... running against this database, so I'm going to need more proof than that. My boss suggested some sort of SELECT trigger, but that doesn't exist (to what I've found) for SQL Server 2000.

So, does anyone know how to determine if a table is being accessed or not (other than manually looking through thousands of lines of code)?

Thanks for any help you can give me.If you happen to have a test system, go ahead and rename the tables, and do a round of testing. Good luck.|||Originally posted by MCrowley
If you happen to have a test system, go ahead and rename the tables, and do a round of testing. Good luck.
I wish it were that simple. There is no test environment that tests all the applications/reports/etc... that are accessing this database. And, if I rename the tables, I've been told that the stored procedures will continue to work properly, while programs executing miscellaneous queries will fail. My initial idea has been to create copies of all the tables that I want to delete (named tablename_DELETE) and drop the original tables. Then if "shit hits the fan", I can rename the backup copy table back to its original form.

This still puts me in a situation where I have a lot of uncertainty about what I will be affecting. That's why I wanted to find some sort of an access log, or SELECT triger, or something I could use to see if these tables were being accessed.|||You can periodically look into syscacheobjects table and see if helps.|||Originally posted by rdjabarov
You can periodically look into syscacheobjects table and see if helps.
Good idea. I'm formulating some sort of a job to check this now, but I really curious to know what happens on instances where a table is accessed sparingly. In this case, would an entry exist in the syscacheobjects?|||What about using SQL Profiler and running a trace?|||Also you could use Profiler for saving tables activity (only selects, inserts, deletes and updates) and analyzing.|||Originally posted by Brett Kaiser
What about using SQL Profiler and running a trace?
You are reading my mind... ;)|||The profiler idea is a good one. I'm going to try that out.

Thanks for the help!|||My only qualm about Profiler is that it may not be able to pick up views an/or procedures that access this table. I know sysdepends did not show anything, but how accurate is that?|||I sometimes log procedure execution by creating the following table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProcedureLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ProcedureLog]
GO
CREATE TABLE [dbo].[ProcedureLog] (
[ProcessName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProcessTime] [datetime] NULL ,
[Application] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CurrentUser] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SystemUser] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProcedureLog] WITH NOCHECK ADD
CONSTRAINT [DF_ProcedureLog_ProcessTime] DEFAULT (getdate()) FOR [ProcessTime]
GO
setuser
GO
EXEC sp_bindefault N'[dbo].[DF_SYS_APPNAME]', N'[ProcedureLog].[Application]'
GO
EXEC sp_bindefault N'[dbo].[DF_SYS_USERNAME]', N'[ProcedureLog].[CurrentUser]'
GO
EXEC sp_bindefault N'[dbo].[DF_STRING_EMPTY]', N'[ProcedureLog].[Notes]'
GO
EXEC sp_bindefault N'[dbo].[DF_STRING_EMPTY]', N'[ProcedureLog].[ProcessName]'
GO
EXEC sp_bindefault N'[dbo].[DF_SYS_SUSERSNAME]', N'[ProcedureLog].[SystemUser]'
GO
setuser
GO

...and then putting this code at the start of each procedure I want to monitor:

--Record the event
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProcedureLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
insert into dbo.ProcedureLog (ProcessName) values ('YourProcedureName')

This monitors who, what, and when a procedure is called.

You could put the same code in a trigger on a table.

Actually, I also include a notes field where I pass the values of all the parameters submitted to the procedure, but this may not be necessary for what you want to do.|||Yeah, except as the poster said, - there are no dependent ptocedures found for the tables that he wants to research the usage of.

You could put the same code in a trigger on a table.

Have they come up with a trigger for SELECT already? Man, the technology is just leaping into the future, leaving us behind every day :)|||Old technology, actually.

Just because a relationship doesn't show up in sysdepends doesn't mean it doesn't exist. Dynamic dependencies are not documented in sysdepends.

And as far as searching for references, maybe you should just script out your database and application code and do string searches for the suspect objects.|||Originally posted by blindman
Old technology, actually.

Just because a relationship doesn't show up in sysdepends doesn't mean it doesn't exist. Dynamic dependencies are not documented in sysdepends.

And as far as searching for references, maybe you should just script out your database and application code and do string searches for the suspect objects.

Nah...he's worried about applcation connecting to the server...

There are no TRIGGERS for SELECT btw

bol

{ [DELETE] [,] [INSERT] [,] [UPDATE] }

Are keywords that specify which data modification statements, when attempted against this table or view, activate the trigger. At least one option must be specified. Any combination of these in any order is allowed in the trigger definition. If more than one option is specified, separate the options with commas.

For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a referential relationship specifying a cascade action ON DELETE. Similarly, the UPDATE option is not allowed on tables that have a referential relationship specifying a cascade action ON UPDATE.|||Thanks, Brett, that's what I thought :)|||Originally posted by Brett Kaiser
Nah...he's worried about applcation connecting to the server...

There are no TRIGGERS for SELECT btw

bol
Yes, I was more worried about external programs running adhoc queries than I was about stored procedures accessing the tables. I felt reasonably confident with the sp_depends, and I even searched the syscomments system table for references to the tables in quetion in the text field. So, basically a SELECT trigger would be an ideal option, but the profiler is serving its purpose for now. I'm going to give it a week, and if I haven't seen any activity by then, I'm smoking the tables.

Thanks again, for all the help