Situatie
Mai jos este o procedura care listeaza toate tabelele nefolosite de la ultimul restart al serverului MSSQL Server. Pe langa tabelele neutilizate este afisata si data ultimului restart al serverului MSSQL.
Solutie
create procedure UnusedTablesFromLastServerRestart
as
declare @LastRestart datetime
select @LastRestart = crdate from sys.sysdatabases where dbid=2;
WITH lastactivity([objectid],
[lastaction])
AS (
SELECT object_id AS [tablename],
[last_user_seek] AS [lastaction]
FROM [sys].[dm_db_index_usage_stats] AS [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] AS [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] AS [u]
WHERE [database_id] = DB_ID(DB_NAME()))
SELECT OBJECT_NAME([so].object_id) AS [tablename],
@LastRestart as LastServerRestart
FROM [sys].[objects] AS [so]
LEFT JOIN [lastactivity] AS [la] ON [so].object_id = [la].[objectid]
WHERE [so].[type] = ‘U’
AND [so].object_id > 100
AND [lastaction] is null
GROUP BY OBJECT_NAME([so].object_id)
ORDER BY OBJECT_NAME([so].object_id);
Leave A Comment?