Find tables and views that are not in use at all?

Question

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

Answer 1

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.

Answer 2

I really would not recommend to delete the tables just like that, without viewing the applications that use this particular database.

But if you are curious to find the tables that do not have any row on them, than you can execute the following statement:

SELECT mySc.name + '.'+ myTbl.name TableName ,SUM(myPrt.rows) RowsPerTable


FROM SYS.Tables myTbl INNER JOIN SYS.Partitions myPrt

ON myPrt.OBJECT_ID = myTbl.OBJECT_ID  INNER JOIN SYS.Schemas mySc
ON myTbl.schema_id = mySc.schema_id WHERE myTbl.is_ms_shipped = 0 AND myPrt.index_id IN (1,0)

GROUP BY mySc.name,myTbl.name
ORDER BY SUM(myPrt.rows) DESC

Than all the tables that will return 0 rows, should be the once that were not used (or the once that someone deleted the data from them).

For the views or other objects, you could check table dependencies.