Developing Matt

My Technical Journal

table last accessed

leave a comment »

The other day I was looking for something to tell me when a stored procedure was last executed.  I found something, but haven’t got it working yet…but in the meantime here’s how to find when a table was last accessed, compliments of Aaron Betrand.  (link to original post here).  Great stuff!  Aaron also talks in the post about sql server 2008 and how it’s capable of server auditing for many actions (as granular as a query against a row and who queried it)

SET ANSI_WARNINGS OFF;

SET NOCOUNT ON;

GO

WITH agg AS

(

    SELECT

        [object_id],

        last_user_seek,

        last_user_scan,

        last_user_lookup,

        last_user_update

    FROM

        sys.dm_db_index_usage_stats

    WHERE

        database_id = DB_ID()

)

SELECT

    [Schema] = OBJECT_SCHEMA_NAME([object_id]),

    [Table_Or_View] = OBJECT_NAME([object_id]),

    last_read = MAX(last_read),

    last_write = MAX(last_write)

FROM

(

    SELECT [object_id], last_user_seek, NULL FROM agg

    UNION ALL

    SELECT [object_id], last_user_scan, NULL FROM agg

    UNION ALL

    SELECT [object_id], last_user_lookup, NULL FROM agg

    UNION ALL

    SELECT [object_id], NULL, last_user_update FROM agg

) AS x ([object_id], last_read, last_write)

GROUP BY

    OBJECT_SCHEMA_NAME([object_id]),

    OBJECT_NAME([object_id])

ORDER BY 1,2;

Advertisements

Written by matt

November 21, 2008 at 4:38 pm

Posted in Sql Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: