Sql-server – Find tables and views that are not in use at all

sql serversql server 2014

I did not find any similar question here, so please direct me if I'm wrong.

I have a large database (inherited from former employee) and I need to clean up that database.

How do I find all tables and views which are not used at all in database?

I found a query at SQL Server script to know when the table accessed last time (CALSQL | SQL Server Community Blog):

with cte_recent as
(
select SCHEMA_NAME(B.schema_id) +'.'+object_name(b.object_id) as tbl_name,
(select MAX(last_user_dt) from (values (last_user_seek),(last_user_scan),(last_user_lookup)) as all_val(last_user_dt)) as access_datetime FROM sys.dm_db_index_usage_stats a
right outer join sys.tables b on a.object_id =  b.object_id
)
select tbl_name,max(access_datetime) as recent_datetime  from cte_recent
group by tbl_name
order by recent_datetime desc , 1

Best Answer

I have used a process from this article by spaghettidba (Gianluca Sartori):

Tracking Table Usage and Identifying Unused Objects

To summarize very briefly:

  1. Search your codebase
  2. Use the index usage stats DMV
  3. Use Extended Events to monitor object access, streaming the events with Powershell

Gianluca provides many code examples in his article.

There is still a lot of risk

After collecting data for 3 months. I have renamed unused table to TBD and then finally removed after 6 - 8 months. Good luck.

Related Question