Home Forums SQL Server 2005 Administering Deleting the tables which are not been used from long time. RE: Deleting the tables which are not been used from long time.

  • you can use the below script to find when the table got accessed last time

    WITH LastActivity (ObjectID, LastAction) AS

    (

    SELECT object_id AS TableName,

    last_user_seek as LastAction

    FROM sys.dm_db_index_usage_stats u

    WHERE database_id = db_id('DBNAME')

    UNION

    SELECT object_id AS TableName,

    last_user_scan as LastAction

    FROM sys.dm_db_index_usage_stats u

    WHERE database_id = db_id('DBNAME')

    UNION

    SELECT object_id AS TableName,

    last_user_lookup as LastAction

    FROM sys.dm_db_index_usage_stats u

    WHERE database_id = db_id('DBNAme')

    )

    SELECT OBJECT_NAME(so.object_id) AS TableName,

    MAX(la.LastAction) as LastSelect

    FROM sys.objects so

    LEFT

    JOIN LastActivity la

    on so.object_id = la.ObjectID

    WHERE so.type = 'U'

    AND so.object_id > 100

    GROUP BY OBJECT_NAME(so.object_id)

    ORDER BY OBJECT_NAME(so.object_id)