I have some databases on an instance used for reporting. I want to merge them into one. There are many cross-database dependencies in the stored procedures (e.g. an SP can use tables from different databases). Besides there are some SSIS packages that point to the different databases when they load data.
I think people who created the databases thought the a database is a folder and they made thier own system (e.g. "let us put these tables in a new database"). All objects are in the dbo schema and the names do not collude. I don't see any benefit of having multiple DBs. But many benefits of having one. E.g. I cannot create indexed views and objects from different databases are involved. I have to monitor the SPs from different databses, etc.
Is there any smart way of mergin the databases, or does everything have to be done manually? Is there a tool, e.g. on Redgate? So everything is on the same instance... Just some tips... Thanks.