Is there a way to define the name of the database as the global name in the sql server?


I have two databases as mentioned below:

  • [QCR_DEV]

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].%';