Suppose I have two databases named DB1 and DB2. In DB1, there is a table named Student, In DB2, there is a stored procedure named SP1. In SP1, I am selecting data of Student Table using below query :
Select *from DB1.dbo.Student.
I have more than 300 stored procedures having above said cross database communication. Now, I want to change my database from DB1 to DB3 that is identical to DB1 from data and schema perspective. For this, I also have to modify all 300 stored procedures that are having fully-qualified database name. Now, the query will likely to be as follows :
Select *from DB3.dbo.Student
I don't want to change all stored procedure to point DB3 now, also don't want to change my queries written in stored procedure into dynamic SQL (I know this can be done by creating dynamic SQL).
Is it possible if We run DB1.dbo.Student, It will redirect to DB3.dbo.Student. Any intermediate layer or any SQL setting.
It'll be very big help for me. Thanks In Advance !!
Aucun commentaire:
Enregistrer un commentaire