Find statistics on each database object accessed, tables, views, stored procedures etc (sql 2008)

• chandra.pottipati

  SSC Veteran

  Points: 238

  More actions

  December 15, 2010 at 11:00 am

  #388442

  I am trying to find a tool that logs access to all the database objects. We recently inherited the database and found out that there are a whole bunch of tables and other objects in there that are not used. We are trying to find all the objects that are being used. Is there a tool/database feature that is available that would log access to all database objects.

  It is a SQL 2000 database but we are going to upgrade to 2008, if a tool is not avalilable for 2000.

  If any one knows about a tool available for 2000 it will be great.

  Thank you.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  December 15, 2010 at 11:16 am

  #1263038

  You may want to test this:

  SELECT

  Owner = USER_NAME(so.UID),

  TableName = so.Name,

  TableID = so.ID,

  Rows = CASE WHEN si.IndID IN (0,1) THEN si.Rows ELSE 0 END,

  /*The RowModCtr column "Counts the total number of inserted, deleted, or

  updated rows since the last time statistics were updated for the table." */

  RowModCtr = si.RowModCtr

  FROM dbo.SysObjects so, dbo.SysIndexes si

  WHERE si.ID = so.ID AND si.IndID IN (0, 1, 255) AND so.XType = 'U'

  AND OBJECTPROPERTY(so.ID,'IsMSShipped') = 0 /*Not an MS object/table*/

  /*AND PERMISSIONS(so.ID) <> 0 AND so.Name LIKE '%Time%'*/

  ORDER BY TableName

  Please note the limitation on the above T-SQL statement

  /*The RowModCtr column "Counts the total number of inserted, deleted, or

  updated rows since the last time statistics were updated for the table." */

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]