After writing the code I posted yesterday I ran it against my development server, then the QA servers, checking applications to ensure they ran fine after I performed the cleanup. Once I was satisfied that the cleanup was non-invasive, I ran the program against my production servers.
Well, everything went well except one application which is written in Cold-Fusion and uses JDBC connections to SQL Server. Almost immediately after I ran the cleanup program I received emails that all the web sites using this application were down. The error message they received was:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Could not find prepared statement with handle 1.
I went into the Java Runtime Console and tested the connection to a half-dozen of the databases and verified that JDBC could successfully connect, but the web sites were still down. I "punted" and rebooted the web servers running the application and as soon as they came back up the web sites were all running again.
Now, this change really should have been transparent - I deleted schemas that owned no objects, but for some reason Cold Fusion reacted badly. What's interesting was that the same problem did not occur in my QA environment for the same application, so the outage was completely unexpected.
My point for this post is that you have to be prepared when doing any cleanup for unexpected consequences.
Allen