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
  • 2
    I think this question is possibly misguided. Do you really just want to throw data away? It'll be in the database for a reason...
    – Philᵀᴹ
    Commented Mar 25, 2016 at 10:25
  • No, first I'll just rename them, then eventually removed if no one will complain and of course save their data before that (just in case).
    – Dada
    Commented Mar 25, 2016 at 10:59

2 Answers 2


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.


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 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

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.

  • Of course I will check a program. I'm doing it with a reason. Application which uses this database is not working properly. And I need to fix the program also.
    – Dada
    Commented Mar 25, 2016 at 11:05
  • The question doesn't seem to be asking about tables that have no rows. A table may have many rows and still have been unused (for a long time). Or the other way around, a currently empty table may pretty much be in active use.
    – Andriy M
    Commented Mar 25, 2016 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.