I've inherited apps that have sprocs that refer to a vendor DB that has different names for dev, uat and prod.
The vendor db to use in the each sroc is a token and is replaced at deployment.
As a result, when refreshing dev or uat from prod the sprocs are still referring to prod and so need to be changed to refer to dev/uat.
Current approach is to drop and recreate all sprocs with the correct dev/uat db substitution applied.
An alternative is to update the system tables by doing a simple search and replace for the prod db with the dev/uat name. Even though they're only dev/uat DBs are could be recovered easily if there was an issue, I suspect I'm unlikely to get this past the DBAs.
Is there a more elegant way of being able to dynamically change the vendor DB used perhaps by the vendor DB being change from being hardcoded to being a reference that can be changed in one place?