CODEWITHSUNDEEP
c#sql-serverdatabase
Umer Waheed
Umer Waheed

Reputation: 4584

Is there any way to set Database name as global name in sql server?

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?

Upvotes: 2

Views: 273

Answers (2)

Y.B.
Y.B.

Reputation: 3586

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

Upvotes: 6

Peter B
Peter B

Reputation: 24147

You could do this in the DEV database:

CREATE VIEW [dbo].[party_log]
AS
  SELECT * FROM [QCR_DEV_LOG].[dbo].[party_log]

Then you can write SELECT-queries as if the [party_log] table exists in the DEV database.

Any WHERE.. or JOIN..ON.. clauses should get applied before the combined query is executed.

If the LOG database ever gets moved or renamed, then you'd only need to update the view (or a couple of views, but probably never a lot).

If you expect regular changes, or if you need to use this on multiple servers then you could use dynamic SQL:

IF OBJECT_ID('[dbo].[party_log]') IS NOT NULL DROP VIEW [dbo].[party_log]
-- etc, repeat to DROP other views

DECLARE @logdb VARCHAR(80) = 'QCR_DEV_LOG'

EXEC ('CREATE VIEW [dbo].[party_log] AS SELECT * FROM [' + @logdb  + '].[dbo][party_log]')
-- etc, repeat to create other views

Upvotes: 5

Related Questions