How to identify unused tables We are planning to merge 2 or 3 region databases who are using almost the same version of SQL Server 2008 R2, have exact tables and most stored procedures for our end users reports.. in the process, we are trying to get rid of all unused objects that may have been created for testing purposes to limit what we see in the production database. So there are 5 or 7 tables that the previous DBA created but is no longer in use (at least the front end application is not using it anymore), how can I make sure that there are nothing else is depending on these tables, triggers or SP? 

Is there a checklist for me to use before I attempt deleting them other than backups, like when was the last time was used, by whom, etc...?
Since a table might only be referenced once a year (think annual reports), and since they may be referenced only in some developer's (or your CEO's) code or spreadsheet on their desktop, these approaches do not work:


tracing the database to see if any tables aren't referenced for a day / week / month
finding some reliable way to determine when the table was last used (this is not easy)
searching the codebase


The safest approach would be to rename the tables that you have already identified through normal means (like the ones above) as likely to be no longer used, and stand back to see if anyone complains. This way you can restore them quite easily if anyone should complain (and hopefully any interim breakage is in low-impact areas, otherwise these tables should have been identified better).

However, you still need to wait a full business cycle before assuming that no news is good news, as per my very first sentence.