How to find Unused Tables or Tables not accessed by any process in DB.

[Doreamon]

Hi Everyone,

I need help in finding unused tables in DB, I need to clean my database and not sure which tables are important. I would like to know the creation date, modification date and last access date for all my tables.

Note : The logs are removed after the SQL Server restart. Please guide me a way to solve this solution.

Is there any possible way, we can track those information even after the server restarts.

Many thanks in advance.

[#2]

---

name,I would like to know the creation date, modification date

---

@Doreamon , the first part is easy, you get the information from system view sys.tables:

select name, create_date, modify_date
from sys.tables
order by name

The second is not that easy. First check if the table(s) are used in any view or procedures = dependencies: View the Dependencies of a Table

Tables can be accessed from any external applications, SSIS or SSAS.

If there are indexes on the tables, then you can check the last usage by DMV sys.dm_db_index_usage_stats (Transact-SQL)

[#3]

@olafhelper 

Hi, Thanks for your reply, but the view sys.dm_db_index_usage_stats was not storing its data after the SQL Server restart. Unfortunately my server restarted on 28th of this month and i am facing challenges in tracking details of scan, update of tables in my DB. The view only has data after 28th May 2021.

Is there any way to get or access the old data in view before server restart.

your time and help is appreciated. 

[#4]

---

but the view sys.dm_db_index_usage_stats was not storing its data after the SQL Server restart.

---

Right, the values of the DMV are not persisted, one reason why I wrote "not that easy".

You can rename some of the table where you think, they may are not used and wait for a month or two if an application errors out.

[#5]

Got it. Thanks for your time.
I followed other work around, created a SQL Job and copied the data in index view every 3 hours to other temp table, so my problem is solved and able to find only useful tables.

Stay Safe.

[#6]

@Doreamon 

Hello, can you share your solution i'm interrested into doing the same thing because i'm not able to clearly identify wich tables are used or not in some databases since the server restart.

Thanks :) 

[#7]

@Martin_J420 

Please find the below view where usage data is stored.

sys.dm_db_index_usage_stats

 You can take this view and filter out which databases you are interested and store this information in other table. This will ensure even the SQL server restarts we have the data in our backup table.

Note : The temporary table should full outer join this view, even the view is empty this will not effect our temp tables data. Hope this will help you.

Thank You.