Forum Discussion
How to find Unused Tables or Tables not accessed by any process in DB.
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.
name,I would like to know the creation date, modification dateDoreamon , 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 nameThe 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.
