SQL SERVER DBA, Linux and Azure: DB last updated and last_select_query_update_script

DB last updated and last_select_query_update_script

--DB last updated 

 select * from sys.dm_db_index_usage_stats where database_id = db_id('database_name') order by last_user_update 

 ---------------------------------------- 

 --last_select_query_update_script 


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)

No comments:

Post a Comment