Situatie
Procedura de mai jos listeaza toate tabelele de pe o baza de date MSSQL Server, iar pentru fiecare tabela afiseaza data ultimei accesari.
Solutie
create procedure LastTabeAccess
as
WITH lastactivity([schemaname],
object_id,
[tablename],
[lastaction],
[type])
AS (
SELECT [ss].[name] AS [schemaname],
[o].object_id AS object_id,
OBJECT_NAME([o].object_id) AS [tablename],
[last_user_seek] AS [lastaction],
[type]
FROM [sys].[dm_db_index_usage_stats] AS [u]
LEFT JOIN [sys].[objects] AS [o] ON [u].object_id = [o].object_id
LEFT JOIN [sys].[schemas] AS [ss] ON [o].schema_id = [ss].schema_id
WHERE [database_id] = DB_ID(DB_NAME())
UNION
SELECT [ss].[name] AS [schemaname],
[o].object_id AS object_id,
OBJECT_NAME([o].object_id) AS [tablename],
[last_user_scan] AS [lastaction],
[type]
FROM [sys].[dm_db_index_usage_stats] AS [u]
LEFT JOIN [sys].[objects] AS [o] ON [u].object_id = [o].object_id
LEFT JOIN [sys].[schemas] AS [ss] ON [o].schema_id = [ss].schema_id
WHERE [database_id] = DB_ID(DB_NAME())
UNION
SELECT [ss].[name] AS [schemaname],
[o].object_id AS object_id,
OBJECT_NAME([o].object_id) AS [tablename],
[last_user_lookup] AS [lastaction],
[type]
FROM [sys].[dm_db_index_usage_stats] AS [u]
LEFT JOIN [sys].[objects] AS [o] ON [u].object_id = [o].object_id
LEFT JOIN [sys].[schemas] AS [ss] ON [o].schema_id = [ss].schema_id
WHERE [database_id] = DB_ID(DB_NAME()))
SELECT [la].[schemaname],
object_id,
[tablename],
[la].[type],
MAX([la].[lastaction]) AS [lastselect]
FROM [lastactivity] AS [la]
WHERE [la].[type] = ‘U’
AND [la].object_id > 100
GROUP BY [la].[schemaname],
object_id,
[tablename],
[la].[type]
ORDER BY [lastselect] desc;
Leave A Comment?