Conoscere la data/ora dell'ultimo user UPDATE su una tabella

dicembre 20, 2008

Come posso conoscere la data e l’ora dell’ultimo UPDATE eseguito su una tabella da parte di un utente ?

Una delle DMVs (Index Related Dynamic Management Views and Functions), disponibili dalla versione 2005 di SQL Server, ci permette di rispondere a questa domanda.

Possiamo infatti utilizzare la DMV sys.dm_db_index_usage_stats che con i sui differenti contatori, basati sulle operazioni eseguite sugli indici, ci permette di conoscere, ad esempio, la data e l’ora dell’ultimo comando UPDATE eseguito su una determinata tabella.

La seguente funzione inline (basata su sys.dm_db_index_usage_stats) accetta in input un parametro di tipo VARCHAR in cui è possibile specificare il nome di una tabella oppure una lista di nomi di tabelle separati da “,” per le quali di desidera conoscere la data/ora dell’ultimo user UPDATE eseguito. Le informazioni richieste verranno restituire sotto forma di tabella.

USE [AdventureWorks]
go

-- Create inline user-defined functions
CREATE FUNCTION dbo.udf_get_update_counter
(@object_name AS varchar(max))
RETURNS TABLE
AS
/*
    Descrizione:
      La funzione restituisce la data e l’ora
      dell’ultimo comando UPDATE eseguito su una
      determinata tabella

    Parametri:
      @object_name = Nome di una tabella o lista di nomi di tabelle
                     separati da “,” per le quali di desidera
                     conoscere la data/ora dell’ultimo UPDATE eseguito
*/
RETURN
(
    SELECT
      object_schema_name(d.object_id) AS object_schema_name,
      object_name(d.object_id) AS object_name,
      MAX(d.last_user_update) AS last_user_update,
      MAX(d.last_system_update) AS last_system_update
    FROM
      sys.dm_db_index_usage_stats d
    JOIN
      sys.objects o ON o.object_id=d.object_id
    WHERE
      ((d.user_updates > 0) OR
       (d.last_system_update > 0)) AND
      ((CHARINDEX((’,’ + LTRIM(RTRIM(object_schema_name(d.object_id) + ‘.’ + o.name)) + ‘,’),
                  (’,’ + REPLACE(@object_name, ’ ‘, ‘’) + ‘,’)) > 0) OR
       (@object_name=’’))
    GROUP BY
      d.object_id
)

Esempio:

Aggiorniamo nell’ordine le tabelle Production.Product e Person.Address presenti nel database AdventureWorks, in cui abbiamo creato la funzione dbo.udf_get_update_counter:

--1
update
production.product
set
ListPrice=(ListPrice + 1)
where
ProductId=1

--2
update
Person.Address
set
City=City
where
AddressID=1

Utilizziamo la funzione dbo.udf_get_update_counter a cui passiamo (come parametro) i nomi delle tabelle per le quali si desidera conoscere la data/ora dell’ultimo UPDATE eseguito:

select
*
from
dbo.udf_get_update_counter(‘Production.Product, Person.Address’)
order by
last_user_update desc

I contatori vengono inizializzati ad ogni:

Avvio del servizio SQL Server (MSSQLSERVER)
Detach del database
Shut down (per esempio, perché AUTO_CLOSE è impostato su ON)