테이블 최종 엑세스 시각 MS SQL server

MS SQL server 기준이며 여러가지로 응용이 가능해 보입니다.

특히나 DBA role을 가지고 있다면...

SQL server DMV는 참 맘에 든다.

 

/* 테이블 최종 엑세스 시각 */

SELECT DB_NAME(A.database_id) AS DbName

     , SchemaName

     , TableName

     , MAX(LastAccess) as LastAccess

  FROM (SELECT sta.database_id as database_id

             , schema_name(schema_id) as SchemaName

             , name as TableName

             , (SELECT MAX(last_access)

                 FROM (values(last_user_seek),

                        (last_user_scan),

                        (last_user_lookup),

                        (last_user_update)) AS tmp(last_access))

                AS LastAccess

          FROM sys.dm_db_index_usage_stats sta

         INNER JOIN  sys.objects obj

            ON obj.object_id = sta.object_id

           AND obj.type = 'U'

           AND sta.database_id = DB_ID()

       ) A

 WHERE TableName IN('테이블명')

 GROUP BY A.database_id

     , SchemaName

     , TableName

 ORDER BY LastAccess DESC;