How to get the last usage of a table

Our main database is never documented or source-controlled!  Probably does not sound new to you.  We have a dozen of application talking to this database all having their own tables and other objects.  Like the database, the applications are not documented either.  So no one knows for sure what the tables are for an application.  I tried this script to see usage of tables.  After running all the applications and using most of the ‘main’ features I got some idea on the tables being used by these application.

 

Here is the script:

 

declare @result table
(
Database_name varchar(50)
,Tablename varchar(50)
,last_user_lookup datetime
,last_user_scan datetime
,last_user_seek datetime
)

insert into @result(Database_name, Tablename, last_user_lookup, last_user_scan, last_user_seek)
SELECT DB_NAME(ius.[database_id]) AS [Database],
OBJECT_NAME(ius.[object_id]) AS [TableName],
MAX(ius.[last_user_lookup]) AS [last_user_lookup],
MAX(ius.[last_user_scan]) AS [last_user_scan],
MAX(ius.[last_user_seek]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS ius
WHERE ius.[database_id] = DB_ID()
--AND ius.[object_id] = OBJECT_ID('tblproperty')
GROUP BY ius.[database_id], ius.[object_id];

select * from @result order by last_user_scan desc

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

I must thank Dave Pinal’s awesome blog sqlauthority (SQL SERVER – Identify Last User Access of Table using T-SQL Script) for the above code.