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
Leave A Comment?