We have on several databases on same instances, some stored procedures have references to tables on other databases. We are in the middle of a big project to take our database under source control. Creating the baselines is not an problem. But after baseline creation, when trying to deploy on other environments, say a local developer instance or staging or alike, this cross reference issue is causing us several headaches. After doing some research and checking on possible approaches I still can't find a reliable way of managing this issues.
One approach proposed is to split baseline creation into "modules" that could be run individually so the cross referenced objects remain in one single script and be run alone. But what if the referenced objects on the other database has changed, then how I can track those changes? Maybe an index was added on referenced table and directly affects performance on the current sp? Or for whatever reason the referenced table changed column name, even deleted that column that is referenced and added a different one? What a nightmare.
So, question is, is there a recommended solution for this issue? For solution I mean, methodology, step-by-step approach, or whatever else that helps getting close to a solution with the deploying issue when having cross reference between database objects.
And for the moment, removing the database cross references is a no go. Way to much application dependent stuff to be corrected/updated and can't be done for the moment.
Not completely sure if this question follows the standards, as it can maybe trigger some opinion based answers. But I don't have more information to throw in for the moment. If you feel it should be flagged, then go.