Microsoft has a solution for us, it’s called the deprecated event trigger, this trigger is fired every time some DDL or DML is executed on an instance and it can record the warning messages in an XML file on the server. There are several ways you could go about it, you make it a policy to have:
- a. the trigger run on your pre-production systems and only “certify” if for product if you are satisfied with the output after testing has completed, or
- b. have it run on all development servers and send the output to the program managers who then give it to the development teams for corrections, or
- c. run it in production, or in a copy of production using a replay trace file, as you would never really know if the users execute the same code as they did in their tests and if the code in pre-prod is actually a “mirror” of the production environment (service packs, code, drivers, settings etc..).
In an ideal world, you should ask yourself the question, what if I would have to pay for the damages if things go wrong. I guess, as a DBA (Default Blame Acceptor) you have a pretty good feel of your company’s culture and policies and perhaps you go for all 3 options, I know I would.