how to find tables that are not using since log time?

  • Hi ,

    i need to find the tables that are not using since long time in the database

    it would be great if you can post the scrpit if any ...

    thanks in advance

    regards,

    kris

  • i use a nice script from sqlmag:

    SELECT

    t.name AS 'Table',

    SUM(i.user_seeks + i.user_scans + i.user_lookups)

    AS 'Total accesses',

    SUM(i.user_seeks) AS 'Seeks',

    SUM(i.user_scans) AS 'Scans',

    SUM(i.user_lookups) AS 'Lookups'

    FROM

    sys.dm_db_index_usage_stats i RIGHT OUTER JOIN

    sys.tables t ON (t.object_id = i.object_id)

    GROUP BY

    i.object_id,

    t.name

    ORDER BY [Total accesses] DESC

    http://www.sqlmag.com/Article/ArticleID/53878/sql_server_53878.html

  • This could work, but keep in mind that the information in sys.dm_db_index_usage_stats is only since the last service start. If your SQL service just started yesterday there won't be any value, but if it started 3 months ago then you can likely get useful information. Here's a version with a filter:

    SELECT t.name AS 'Table',

    SUM(i.user_seeks + i.user_scans + i.user_lookups) 'Total accesses' ,

    SUM(i.user_seeks) AS 'Seeks',

    SUM(i.user_scans) AS 'Scans',

    SUM(i.user_lookups) AS 'Lookups'

    FROM sys.dm_db_index_usage_stats i

    RIGHT OUTER JOIN sys.tables t ON (t.object_id = i.object_id)

    GROUP BY i.object_id, t.name

    HAVING ISNULL(SUM(i.user_seeks + i.user_scans + i.user_lookups),0) < 2

    ORDER BY [Total accesses] DESC

  • Is there any script which you could share with us for use in SQL Server 2000

    M&M

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply