When the table was last accessed (Unused Tables)- SQL Server Script

Leave a comment (3) Go to comments

Unused tables, DBA should keep a list of tables which are not being used / referenced by anyone in last few weeks. At times, while interacting with application team, we come us with a requirement where developer asks, can I have  list of unused tables or “Can you tell me the tables which are not being used in last one month”

Here is the solution to this. Following Script will tell you the User Table name and the last date and time when a select query was executed on this objects.

Script to find out When the table was last accessed since the SQL Services were running

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(db_name())

UNION

SELECT object_id AS TableName,last_user_scan as LastAction

FROM sys.dm_db_index_usage_stats u

WHERE database_id = db_id(db_name())

UNION

SELECT object_id AS TableName,last_user_lookup as LastAction

FROM sys.dm_db_index_usage_stats u

WHERE database_id = db_id(db_name())

)

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)

Note : DMV’s keeps the data from server start point. If your SQL server was recently restarted then do not use this query.

OUTPUT

When the table was last accessed (Unused Tables)  SQL Server Script When the table was last accessed sql server Unused Tables sql server list of unused tables sql server deleting unused tables sql server delete unused table sql server database cleaning SQL Script table Last used identifying unused objects in a database

The Output will give you the table name and last select, date and time, when the query was executed.

EOF - When the table was last accessed (Unused Tables)- SQL Server Script, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

3 Comments.

  1. This is awesome! I am in charge of identifying, renaming, and then later archiving/deleting old tables (and SPs) and this will really help with identifying! I can’t thank you enough!

  2. There are a few problems with this query. It will never give info on a heap table without indexes. If a table hasn’t been accessed since the server has restarted it will not be reported on. You have to be connected to the db of interest in order to get information on that DB. The query is immensely complex, for something extremely simple. If views are indexed, they will be listed as a table.
    I have two query sets separated by a “GO” statement. The first set is the simple version of the query. The second queries all databases, lists all and only actively viewed tables first and then lists tables in all Dbs that have no history of access and won’t include views in either result set:
    declare @id int= db_id(db_name())
    SELECT o.name AS TableName, MAX(u.Last_User_Seek) AS LastAction
    FROM sys.dm_db_index_usage_stats u
    join sys.sysobjects o ON o.id=u.object_id AND u.database_id=@id AND o.type=’u’
    GROUP BY o.name
    ORDER BY LastAction
    GO
    declare @tbl table (database_name sysname, object_id int, LastAction datetime)
    INSERT @tbl
    SELECT d.name as database_name, u.object_id, max(u.Last_User_Seek) as LastAction
    FROM sys.databases d
    join sys.dm_db_index_usage_stats u ON u.database_id=d.database_id
    WHERE u.Last_User_Seek IS NOT NULL
    GROUP BY d.name, u.object_id
    ORDER BY d.name, LastAction
    declare @tblnm table (database_name sysname, object_id int, TableName sysname, LastAction datetime)
    declare @DBnm sysname, @cmd varchar(max), @crs cursor
    set @crs = CURSOR FAST_FORWARD FOR
    SELECT name FROM sys.databases
    OPEN @crs
    FETCH NEXT FROM @crs INTO @DBnm
    WHILE @@FETCH_STATUS=0
    BEGIN
    SET @cmd = ‘select ”’ + @DBnm + ”’, id, name
    FROM ‘ + @DBnm + ‘.sys.sysobjects o where o.type=”u”’
    INSERT @tblnm (database_name, object_id, TableName)
    EXEC (@cmd)
    FETCH NEXT FROM @crs INTO @DBnm
    END
    UPDATE a
    SET LastAction=b.LastAction
    FROM @tblnm a
    JOIN @tbl b ON b.object_id=a.object_id AND b.database_name=a.database_name
    SELECT a.database_name, a.TableName, a.LastAction from @tblnm a
    WHERE LastAction is not null
    ORDER BY a.database_name, a.LastAction, a.TableName
    SELECT a.database_name, a.TableName from @tblnm a
    WHERE LastAction is null
    ORDER BY a.database_name, a.TableName

  3. FYI here is my improved version :grin:

    WITH LastActivity
    AS
    ( SELECT [object_id], Last_User_Seek as LastAction
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = db_id()
    UNION
    SELECT [object_id], last_user_scan as LastAction
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = db_id()
    UNION
    SELECT [object_id], last_user_lookup as LastAction
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = db_id()
    )

    SELECT so.[type], OBJECT_NAME(so.[object_id]) AS TableName, so.create_date, so.modify_date,
    LastSelect
    FROM
    sys.objects so
    left join
    ( select [object_id], MAX(LastAction) as LastSelect
    from LastActivity
    group by [object_id]
    ) la ON la.[object_id] = so.[object_id]
    left join sys.sql_modules M ON M.[object_id] = so.[object_id]

    WHERE so.[object_id] > 100
    and ( so.type = ‘U’ – user table or
    or ( so.type = ‘V’ – indexed view
    and M.is_schema_bound = 1
    )
    )
    and ISNULL(la.LastSelect,’19010101′) < DATEADD(day,-30,getdate()) – unused within last 30 days
    ORDER BY TableName

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.