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:

  1. tracing the database to see if any tables aren't referenced for a day / week / month
  2. finding some reliable way to determine when the table was last used (this is not easy)
  3. 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.

  • Thanks Aaron for your quick reply, I will check your link for sure and start tracing when was the last time used. But what did you mean by "Searching the codebase"? Renaming them is a good suggestion. Thanks again. Aug 25 '14 at 18:19
  • Well you could search sys.sql_modules.definition, for example, but I have no idea what constitutes your application outside of SQL Server, whether you have that code in source control, etc. It's not just about the database, remember. Aug 25 '14 at 18:27
  • @ShaymaAhmad - look at en.wikipedia.org/wiki/Codebase
    – Hannah Vernon
    Aug 25 '14 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.