You can't automatically do it, some decisions have to be made. Also remember a functionality may exist that would use a table in the database, but just hasn't been used by your users YET. arbitrarily dropping tables without reviewing the code of your application is not a responsible way to tackle this kind of issue.
To at least identify potential tables to drop, though,
Here's how i would do it:
restore a database from 6 months ago.
compare the count(*) of all rows
in OldDB to CurrentDB. and tables that have the same count potentially are not used (or are lookup tables that never change, like a list statuses,counties or states)
of those tables that had the same count, and also are not lookup tables,
I would examine their results from one of the many "Compare Two Databases"
scripts from here on SSC.
those that don't have changes might meet the drop criteria after the application code is reviewed.
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!