SQL Table hit count

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[TableHitCount](

       [DateOn] [date] NULL,

       [DatabaseName] [nvarchar](128) NULL,

       [TableName] [sysname] NOT NULL,

       [NbrTimesAccessed] [bigint] NULL,

       [dateInserted] [datetime] NULL

) ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[TableHitCount] ADD  CONSTRAINT [DF_TableHitCount_dateInserted]  DEFAULT (getdate()) FOR [dateInserted]

GO

 

Insert into Test..TableHitCount ([DateOn]

           ,[DatabaseName]

           ,[TableName]

           ,[NbrTimesAccessed])

 

SELECT

       convert(date,getdate()) DateOn,db_name(ius.database_id) AS DatabaseName,

       t.NAME AS TableName,

       SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS NbrTimesAccessed

 

FROM sys.dm_db_index_usage_stats ius

INNER JOIN sys.tables t ON t.OBJECT_ID = ius.object_id

WHERE database_id = DB_ID('reportserver')

GROUP BY database_id, t.name

ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC