Pamela Mooney has a two-part series on dropping database objects. Part one includes a big setup script:
Some months ago, a fellow DBA came to me and expressed her concern over the normal housecleaning process that occurred at her company. Developers or product owners would submit changes for objects which were no longer used, only to find that sometimes, they were. No damage had been done, but the possibility of creating an emergent situation was there.
I could well understand her concern. Most of us who have been DBAs for any length of time have probably come across a similar scenario. I thought it would be fun to write a process that reduced the risk of dropping database objects and made rollbacks a snap.
Part 2 handles the actual drops:
Now, the objects in the table will be dropped after 120 days. But what if you need them to be dropped before (or after)? Both options work, but I’ll show you the before, and also what happens if there is a problem with the drop.
Check it out and drop with impunity. Or at least the bare minimum of punity.