Warm tip: This article is reproduced from stackoverflow.com, please click
sql-server

SQL View / Function Select statement changes based on Database Connection

发布于 2020-03-27 10:20:55

I have a situation where my production and development (and QA too) databases all have different names. The application uses Views to reference tables in other databases. Rather than having separate scripts in source control for each (dev, qa, production) I'd like to create one 'master' script (view or function?) which will run on all but will select against the appropriate database based upon where (current database) it is run from.

Something like:

CASE db_name() 
WHEN 'dev' THEN (SELECT a,b... FROM DEVSERVER.DEVOTHERDB.dbo.mytable)
WHEN 'prod' THEN (SELECT a,b... FROM PRODSERVER.PRODOTHERDB.dbo.mytable)
...

Any ideas?

Questioner
Matt Balent
Viewed
68
分享
Stu 2019-07-03 22:05

Roughly, the first thing you run is

If Object_Id('dbo.CreateSynonym') Is Not Null Drop Procedure dbo.CreateSynonym
Go

Create Procedure dbo.CreateSynonym
  @TableName SysName
As
Begin
  Declare @Sql NVarchar(Max) = 'If Exists(Select * From sys.synonyms where name = ''' + @TableName + ''''
  Exec (@Sql)

  Set @Sql = 'Create Synonym dbo.' + @TableName + ' For ' +
     Case When Db_Name() = 'dev' Then 'devserver.devdb' 
          When Db_Name() = 'prod' Then 'prodserver.proddb' End + '.dbo.' + @TableName 

  Exec (@Sql)
End

Go

Exec dbo.CreateSynonym 'mytable'
Exec dbo.CreateSynonym 'myothertable'
...
...
Go

(From the top of my head, might have warts) From then on, you just reference dbo.MyTable.

Matt Balent 2019-07-08 20:50:18

Thanks for the insight.

Related issues

1
Why "LAST_VALUE(birthdate)" window function clause necessary compare to "FIRST_VALUE(birthdate)"?
2
Group by query with rollup
3
SQL Server. How to refresh the intellisense?
4
Error "Cannot insert explicit value for identity column in table" using C#
5
SQL query pivot, move values to top
6
Best way to implement an audit trail in SQL Server?
7
TLS 1.2 SQL Server and Delphi Client
8
Count amount of same value
9
SQL Insert does column order matter
10
SQL Server query problem. example is in excel sheet picture