SQL Server - rename and merge databases with legacy support

Question

I have two Sql Server 2008 R2 databases, called MyDbOne and MyDbTwo.

I'd like to merge them (all tables and procedures) into a new database called MyDb.

Assume there are no conflicts in table and procedure names between them.

The problem is: there are LOTS of code and procedures that execute queries using the database name, including procedures declared in one of the databases referecing tables from the other. There are queries like:

select * from MyDbOne..SomeTable;
select * from MyDbTwo..AnotherTable;

The tables SomeTable and AnotherTable would then exist in the MyDb database. But I need to support querying them using the leagcy names MyDbOne and MyDbTwo. If I would run the queries above, I'd like them to be effectively the same as:

select * from MyDb..SomeTable;
select * from MyDb..AnotherTable;

Is there some way to do it? Maybe create some sort of global aliases on the new database, resolving MyDbOne and MyDbTwo to MyDb? That would be perfect, but I don't know how to do it.

Thank you ver much!

Answer 1

I don't think there is an issue solution to this. If I were you, I would keep existing MyDbOne and MyDbTwo databases intact and create a brand new one that references these two tables via views. Basically, you will create a Data Warehouse that seats on top of these existing databases. Good thing about this setup is, you don't have to rewrite any legacy code, which is huge.

Answer 2

There's no easy way around it. You'll have to update all those references manually. You can search the SQL server for all objects containing those database names by using one of these queries from this answer;

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%Foo%' 
AND ROUTINE_TYPE='PROCEDURE'

SELECT OBJECT_NAME(id) 
FROM SYSCOMMENTS 
WHERE [text] LIKE '%Foo%' 
AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
GROUP BY OBJECT_NAME(id)

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%Foo%'