Ultimul select efectuat pe tabele

Configurare noua (How To)

Situatie

Mai jos este o procedura care, rulata pe un server Microsft SQL, returneaza toate tabelele de pe baza pe care e rulata si pentu fiecare tabela, returneaza data cand s-a facut ultimul select. Procedura are parametru astfel incat se poate rula si pentru o anumita tabela.

Solutie

alter procedure LastSelectOnATable_prc
@NumeTabela varchar(max) = null
as
if @NumeTabela is null
begin
WITH LastSelect (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 LastSelect 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)
end
else
begin
WITH LastSelect (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 LastSelect la
  ON so.object_id = la.ObjectID
   WHERE so.type = ‘U’
AND so.object_id > 100
and OBJECT_NAME(so.object_id) = @NumeTabela
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)
end

Tip solutie

Permanent

Voteaza

(8 din 15 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?