Detecting Tables with no recent activity

Is there an efficent way to determine which tables in a SQL Server database
have had no activity reccently. This includes inserts, updates, deleted
and/or selects.
Are there any tools or techniques to find these tables?
All help appreciated!
Tim O wrote:
> Is there an efficent way to determine which tables in a SQL Server
> database have had no activity reccently. This includes inserts,
> updates, deleted and/or selects.
> Are there any tools or techniques to find these tables?
> All help appreciated!
Not inherently, as far as I know. The system tables do not track changes
to underlying objects. You can easily implement some very simple
triggers on the tables you are trying to track using something like the
following. This implementation tracks changes to a table at most once
each day. Using the Ignore_dup_key option on the clustered index tells
SQL Server to ignore duplicates, preventing you from having to manually
check the table each time. You could, of course, improve on this concept
and add a user column and add the suser_sname() value to it and the
index.
CREATE TABLE [testing] (
[col1] [int] NULL)
go
create table TableAccess(TblNm nvarchar(128) NOT NULL, AccessDate
char(8) NOT NULL)
go
create unique clustered index TableAccess_idx on TableAccess (TblNm,
AccessDate) with ignore_dup_key
go
create trigger testing_change on testing
for insert, update, delete
as
Begin
insert into TableAccess (TblNm, AccessDate) Values ('testing',
CONVERT(char(8), getdate(), 112))
End
go
insert into testing values (5)
insert into testing values (6)
insert into testing values (7)
David Gugick
Imceda Software
www.imceda.com
|||"Tim O" wrote:

> Is there an efficent way to determine which tables in a SQL Server database
> have had no activity reccently. This includes inserts, updates, deleted
> and/or selects.
> Are there any tools or techniques to find these tables?
> All help appreciated!
The use of TRIGGERS will not detect SELECTS that are executed on a TABLE as
far as I know!
I need an EFFICINIENT WAY to detect table that are neither read for written
to.
I am also concerned with the overheads that may be placed on the system.
There are a large number of tables and a high degree of daily activity!
Cheers Tim O
|||Tim O wrote:
> The use of TRIGGERS will not detect SELECTS that are executed on a
> TABLE as far as I know!
> I need an EFFICINIENT WAY to detect table that are neither read for
> written to.
> I am also concerned with the overheads that may be placed on the
> system. There are a large number of tables and a high degree of daily
> activity!
> Cheers Tim O
If you are using stored procedures, you can log access from there, even
on SELECTs. The overhead is minimal on insert activity. You can place
the table on a secondary filegroup or in another database, if necessary.
David Gugick
Imceda Software
www.imceda.com
|||Tim,
There is a column in sysindexes for the object that keeps a count of
modifications to the table. The column is rowmodctr. SQL uses this to keep
track of when to run Update Stats when "Auto Update Stats" is on. If after
running update stats for that table, the value rises above 0, then there are
modifications to it. For more info please see:
http://support.microsoft.com/default...en-us%3B195565
As far as Selects, you can run profiler (trace) to see if it is called using
the Filter "TextData". You can also see if it is embedded in any stored
procs, triggers via sp_depends <table_name>. This won't indicate any
references from outside the current DB, tho.
Hope some of this helps.
Steve
"Tim O" <TimO@.discussions.microsoft.com> wrote in message
news:31F8C451-62F7-480F-9362-736E07698FFF@.microsoft.com...[vbcol=seagreen]
>
> "Tim O" wrote:
database
> The use of TRIGGERS will not detect SELECTS that are executed on a TABLE
as
> far as I know!
> I need an EFFICINIENT WAY to detect table that are neither read for
written
> to.
> I am also concerned with the overheads that may be placed on the system.
> There are a large number of tables and a high degree of daily activity!
> Cheers Tim O