מרץ10
Written by:
ronen ariely
10/03/2021 09:33 
Background
Once more, I got the request to help in cleaning the database from unused tables, and once more I find it important to clarify that we should not be reckless to drop a table or any other entity if you are not familiar with your database structure and you have no understanding what this object is used for or why it was created. It can come back to you to bite you in the ass in a month or maybe a few years.
The problem is related to the designing of the database and the desire to turn the wheel back or go back in history and change the structure is a familiar phenomenon, but it is not always a simple task.
The different between an "ok" DBA and a great architect is that the "ok" DBA think about what we need today and the great architect think about what we might need in the future. Managing names and using prefix or postfix for example could do the trick to help understand the relations and the uses f these elements. If your database was well design then there is a good chance that you did not have this issue today.
This request is very common and there are multiple posts online which you can find and read more. This post is a reply to specific request and present my general point-of-view and approach. It is NOT a full solution.
The most important point to understand is that, If you do not set up monitoring of the actions in advance, then there is no way that ensures certainty to retroactively detect unused objects, because the server does not save the full information by default.
With that being said, there are ways to get information which can help us in the task (better say in getting the decisions), even so it will not be full information.
What can we do to get some information about unused and un needed tables?
>> Actions of changing the data or structure of the database can be found usually because those are most likely recorded in the transaction log file, but detecting queries that select the data from the tables is something else.
You can use fn_dblog to read the transaction log and you can use fn_dump_dblog to read the transaction log backup files.
>> You can map all tables using the sys.tables and work in an elimination method, marking the tables which are in used and focus on the unknown.
Important! You should remember that the issue is not only tables but all entities in the database which might not longer be used or might be related to tables which seems like not in used but are relted to these entities. This task is not a simple one!
>> You can use the sys.dm_db_index_usage_stats system DMV which return the time different types of index operations was last performed. When a table is used then the relevant indexes are used with actions like seek, scan, lookup, or update and those actions are returned by the DMV.
Note! The counters are cleared when the server restart, which mean you cannot count on this if your server was restart during the time that you want to check the use of the tables.
>> sys.all_objects system table can help you find when DDL actions were performed on the tables.
>> You can view a table's dependencies using sys.sql_expression_dependencies and you can get database object dependencies using sp_depends. This way you can see if a table or other object is related to another table or object, which mean that the table might be needed indirectly.
Note! unfortunately this will not cover all dependencies and you cannot count on this (again it is only a tool to help you decide what to do). For example a table which is used in dynamic query or CLR will not be detected this way.
Before you drop anything!
Note! BEFORE YOU START ANYTHING MAKE SUER THAT YOU HAVE A FULL BACK OF THE DATABASE, which you tested and confirmed that it can be used to restore the system.
Note: A good approach for removing objects is to monitor the uses of the object first using Extended Events (in addition to all mentioned above) for the time that you consider as "not in used".
Secondly just as last step before dropping the the table you can give it "a last chance" by rename the objects and give it a specific prefix or postfix to mark it as not in used and give it some time (according to what you considered as "not in used").
Only drop the table once you are sure it is not in used directly or indirectly and after you have a full backup of the database!