Aflarea datei ultimului select pe o tabela

Situatie

Pentru cazurile cand avem nevoie sa aflam data ultimului select pe o tabela sau un view, am facut o procedura ce primeste ca parametru numele unei tabele sau a unui view si returneaza, daca gaseste tabela/view-ul in baza de date pe care este rulata, data ultimului select efectuat pe acea tabela sau view, precum si tipul obiectului gasit.

Solutie

create procedure BD_LastSelect_prc
@TableName varchar(max)

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,
CASE WHEN so.type = ‘U’ THEN ‘Table (user-defined)’
WHEN so.type = ‘V’ THEN ‘View’
END AS Table_View

into #temp

FROM sys.objects so
LEFT JOIN LastActivity la
on so.object_id = la.ObjectID
LEFT JOIN sys.tables st
on so.object_id = st.object_id
LEFT JOIN sys.views sv
on so.object_id = sv.object_id

WHERE so.type in (‘V’,’U’)
AND so.object_id > 100

GROUP BY OBJECT_NAME(so.object_id)
, so.type
,st.create_date
,st.modify_date
,sv.create_date
,sv.modify_date

declare @UpperName varchar(max)
set @UpperName = upper(@TableName)

select * from #temp where upper(TableName) like @UpperName

Tip solutie

Permanent

Follow Us