I have two databases as mentioned below:
All application data are stored in [QCR_DEV]. On each table of [QCR_DEV], there is a trigger that insert the details of insertion and update of [QCR_DEV] table into [QCR_DEV_LOG] database.
Suppose i have a table [party] in [QCR_DEV] database. Whenever i insert,update or delete some record in the table. There will be one insertion in table [party_log] which exists in [QCR_DEV_LOG] database. In short i am keeping the log or action performed on tables of [QCR_DEV] into [QCR_DEV_LOG] database.
When we connect to database through application, it connect to database somehow using connection-string. In my stored procedure, i did not use database name like:
Select * From [QCR_DEV].[party];
I am using like this:
Select * From [party];
This is because, in feature if i need to change database name then i will only need to change connection-string.
Now come to the point, i need to get data from [QCR_DEV_LOG] database. I am writing a stored procedure in which i need to get data from both databases like:
Select * From [QCR_DEV_LOG][party_log] INNER JOIN [person] on [person].person_id = [QCR_DEV_LOG][party_log].person_id where party_id = 1
This stored procedure is in [QCR_DEV] database. I need to get data from both databases. For this i need to mention the database name in query. I don't want this. Is there any way to set database name globally and use this name in my queries so that if in future i need to change database name, i only change from where it sets globally. Is there any way to do this?
I would second Jeroen Mostert comment and use synonyms:
CREATE SYNONYM [party_log] FOR [QCR_DEV_LOG].[dbo].[party_log];
And when the target database is renamed, this query would generate a migration script:
SELECT 'DROP SYNONYM [' + name + ']; CREATE SYNONYM [' + name + '] FOR ' + REPLACE(base_object_name, '[OldLogDbName].', '[NewLogDbName].') + ';' FROM sys.synonyms WHERE base_object_name LIKE '[OldLogDbName].%';