Unused tables, DBA should keep a list of tables which are not being used / referenced by anyone in last few weeks. At times, while interacting with application team, we come us with a requirement where developer asks, can I have list of unused tables or “Can you tell me the tables which are not being used in last one month”
Here is the solution to this. Following Script will tell you the User Table name and the last date and time when a select query was executed on this objects.
Script to find out When the table was last accessed since the SQL Services were running
|
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 FROM sys.objects so LEFT JOIN LastActivity 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) |
Note : DMV’s keeps the data from server start point. If your SQL server was recently restarted then do not use this query.
OUTPUT
The Output will give you the table name and last select, date and time, when the query was executed.
This is awesome! I am in charge of identifying, renaming, and then later archiving/deleting old tables (and SPs) and this will really help with identifying! I can’t thank you enough!
There are a few problems with this query. It will never give info on a heap table without indexes. If a table hasn’t been accessed since the server has restarted it will not be reported on. You have to be connected to the db of interest in order to get information on that DB. The query is immensely complex, for something extremely simple. If views are indexed, they will be listed as a table.
I have two query sets separated by a “GO” statement. The first set is the simple version of the query. The second queries all databases, lists all and only actively viewed tables first and then lists tables in all Dbs that have no history of access and won’t include views in either result set:
declare @id int= db_id(db_name())
SELECT o.name AS TableName, MAX(u.Last_User_Seek) AS LastAction
FROM sys.dm_db_index_usage_stats u
join sys.sysobjects o ON o.id=u.object_id AND u.database_id=@id AND o.type=’u’
GROUP BY o.name
ORDER BY LastAction
GO
declare @tbl table (database_name sysname, object_id int, LastAction datetime)
INSERT @tbl
SELECT d.name as database_name, u.object_id, max(u.Last_User_Seek) as LastAction
FROM sys.databases d
join sys.dm_db_index_usage_stats u ON u.database_id=d.database_id
WHERE u.Last_User_Seek IS NOT NULL
GROUP BY d.name, u.object_id
ORDER BY d.name, LastAction
declare @tblnm table (database_name sysname, object_id int, TableName sysname, LastAction datetime)
declare @DBnm sysname, @cmd varchar(max), @crs cursor
set @crs = CURSOR FAST_FORWARD FOR
SELECT name FROM sys.databases
OPEN @crs
FETCH NEXT FROM @crs INTO @DBnm
WHILE @@FETCH_STATUS=0
BEGIN
SET @cmd = ‘select ”’ + @DBnm + ”’, id, name
FROM ‘ + @DBnm + ‘.sys.sysobjects o where o.type=”u”’
INSERT @tblnm (database_name, object_id, TableName)
EXEC (@cmd)
FETCH NEXT FROM @crs INTO @DBnm
END
UPDATE a
SET LastAction=b.LastAction
FROM @tblnm a
JOIN @tbl b ON b.object_id=a.object_id AND b.database_name=a.database_name
SELECT a.database_name, a.TableName, a.LastAction from @tblnm a
WHERE LastAction is not null
ORDER BY a.database_name, a.LastAction, a.TableName
SELECT a.database_name, a.TableName from @tblnm a
WHERE LastAction is null
ORDER BY a.database_name, a.TableName
FYI here is my improved version :grin:
WITH LastActivity
AS
( SELECT [object_id], Last_User_Seek as LastAction
FROM sys.dm_db_index_usage_stats
WHERE database_id = db_id()
UNION
SELECT [object_id], last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats
WHERE database_id = db_id()
UNION
SELECT [object_id], last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats
WHERE database_id = db_id()
)
SELECT so.[type], OBJECT_NAME(so.[object_id]) AS TableName, so.create_date, so.modify_date,
LastSelect
FROM
sys.objects so
left join
( select [object_id], MAX(LastAction) as LastSelect
from LastActivity
group by [object_id]
) la ON la.[object_id] = so.[object_id]
left join sys.sql_modules M ON M.[object_id] = so.[object_id]
WHERE so.[object_id] > 100
and ( so.type = ‘U’ – user table or
or ( so.type = ‘V’ – indexed view
and M.is_schema_bound = 1
)
)
and ISNULL(la.LastSelect,’19010101′) < DATEADD(day,-30,getdate()) – unused within last 30 days
ORDER BY TableName