Forum Discussion
How to find Unused Tables or Tables not accessed by any process in DB.
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)
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.
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.
- 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.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 🙂
