Hello,

This stored proc collects the information from the monopenobjectactivity table and saves the data in TablesUsed table.

CREATE PROCEDURE TablesUsed (
@wait_time char(8) = '00:10:00',
@loop int = -10
)
AS
BEGIN

SELECT * into #temp1
from master..monOpenObjectActivity

SELECT * into #temp2
from master..monOpenObjectActivity


INSERT INTO TablesUsed
(DBName,TableName,IndexID,Operations,RowsInserted, RowsDeleted,RowsUpdated,LockWaits,LogicalReads,Phy sicalReads,UsedCount,LastUsedDate,FromDate,ToDate

SELECT db_name(U2.DBID),object_name(U2.ObjectID, U2.DBID),U2.IndexID,U2.Operations,U2.RowsInserted, U2.RowsDeleted,U2.RowsUpdated,U2.LockWaits,U2.Logi calReads,U2.PhysicalReads,(U1.UsedCount -U2.UsedCount),U2.LastUsedDate,dateadd (mi,@loop,getdate()),getdate()
from #temp1 U1 , #temp2 U2
where Object_name(U1.ObjectID,U1.DBID)=Object_name(U2.Ob jectID,U2.DBID)
and db_name(U1.DBID)=db_name(U2.DBID)
and U1.IndexID=U2.IndexID
and U1.IndexID = 0

END
go

Above is the stored procedure which i wrote to monitor the monopenobjectactivity table. DBA will run this stored proc for about 2 months. then i am thinking to query the "TablesUsed" table to find the unused tables. below is the query

select * from TablesUsed
where RowsInserted = 0 and RowsUpdated = 0 and RowsDeleted = 0
order by LogicalReads asc

My question is, can i consider this result where all the RowsInserted, RowsUpdated and RowsDeleted = 0's as unused tables in the database?

when I am observing the resultset of this above query, i am observing that even though RowsInserted, Deleted or Updated are 0 but we have Operations , Logical and Physical reads on that table...so does it mean that the table is being used?

if so, can anyone please guide me, how to write the query to find the unused tables?

Thanks in advance.