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

  • lazy writer

    Ten Centuries

    Points: 1126

    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

  • bagofbirds-767347

    Ten Centuries

    Points: 1110

    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

  • Toby White

    SSCertifiable

    Points: 6127

    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

  • M&M

    SSC-Insane

    Points: 21699

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

    M&M

  • MANU-J.

    SSC-Dedicated

    Points: 31126

    Moin,

    Check http://www.sqlservercentral.com/articles/Administration/2582/

    Manu

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

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