CODEWITHSUNDEEP
sql-serverdatabaset-sqldeployment
Costa
Costa

Reputation: 4085

How to access different databases without writing database names in query

If I created a stored procedure on Database1. and the query was accessing Database2.
Production environment is not having the same database names of development environment.
Now I have to check every procedure in order to change Database2 to Database_Prod2.

This is error prone. How I avoid using database names in a query. or How I avoid this deployment problem given that database names must be different?

SELECT *
FROM [dbo].[MyTable] tab1
INNER JOIN [DB2].[dbo].[MyOtherTable] tab2 ON tab1.ID = tab2.ID

should be changed to 

SELECT *
FROM [dbo].[MyTable] tab1
INNER JOIN [DB_Prod2].[dbo].[MyOtherTable] tab2 ON tab1.ID = tab2.ID

Upvotes: 0

Views: 221

Answers (2)

db9dreamer
db9dreamer

Reputation: 1715

There are many solutions to this problem which you can use, depending on budget/complexity. These are my favourites:-

  1. Duplicate your live environment in a virtual environment and code against the "safe" copy.
  2. Use views to isolate your logic from your tables. These views can be created very easily from the information schema so they have identical structures to the underlying tables. You can then target the views at whichever "live" database you want.
  3. Create synonyms for all your schema objects and only ever code against them. Switch the target of the synonyms as you deploy your solution. reference

Duplicating your environment is the most flexible solution - but it's also the most complex and expensive. It gives you the opportunity to completely isolate your developers from your live system (certification?) and data. The discipline of packaging a deployment in a repeatable and testable way is also very desirable.

Using views and synonyms is a simpler/cheaper solution - but requires more discipline and code review, etc.

Upvotes: 1

Georgy Grigoryev
Georgy Grigoryev

Reputation: 873

Use dynamic statements:

CREATE PROC (@DBName varchar(100)
  --other params
)
AS 
BEGIN 
DECLARE @SQL VARCHAR(MAX) =''
SET @SQL =
'
SELECT *
FROM [dbo].[MyTable] tab1
INNER JOIN ['+@DBName+'].[dbo].[MyOtherTable] tab2 ON tab1.ID = tab2.ID 
'
EXEC(@SQL)
END

Upvotes: 0

Related Questions