How to change my DB name in a query dynamically, but without exec a string?

Question

I have many DB in my SQL server, sometimes we put a copy of the QA DB server for test purposes, then I want to have a variable that select a value from a Table that indicates the name of the DB and change in my function, with no need of rewriting the function for changing the name of the DB, or executing a string to do this. How can I do this?

You can't do this without dynamic SQL and you certainly can't do it in a function. — Aaron Bertrand
– Aaron Bertrand, Commented Oct 22, 2013 at 16:04
If I'm understanding the question correctly, you might be able to do this with a linked server. If your QA server were a linked server on your other DB's server, you could query it using [ServerName].[DbName].[Schema].[Table]. For example: SELECT * FROM [QAServer].[QADatabase].[dbo].[MyTable]. If you're not careful, though, this can cause poor performance (or security holes, in some cases) — valverij
– valverij, Commented Oct 22, 2013 at 16:38
I don't get how you expect to change the name of the db in the function but no need of rewriting the function. — paparazzo
– paparazzo, Commented Oct 22, 2013 at 17:57
I mean, to do something very generic, rewrite all the functions, but then when I need to switch of db name, there would be no need of change all the functions names of DB. — Artemination
– Artemination, Commented Oct 22, 2013 at 18:17

AlexK · Accepted Answer · 2013-10-22 16:42:02Z

3

CREATE SYNONYM Cust FOR DB1.dbo.Customer
GO

SELECT * FROM Cust
GO

DROP SYNONYM Cust
CREATE SYNONYM Cust FOR DB2.dbo.Customer
GO

SELECT * FROM Cust

This technique has limitations, but in some cases it is very useful: MSDN CREATE SYNONYM

answered Oct 22, 2013 at 16:42

AlexK

10.1k2 gold badges25 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

valverij Over a year ago

This is neat, I didn't know you could do this. Won't this only work if the two databases are on the same server, though? Or does it also work for linked servers?

Artemination Over a year ago

Is a good idea, but my DB has a lot of tables, it could work for some cases, but I'm trying to find a generic solution. (Let me read you link)

AlexK Over a year ago

Did you read my link? Remote or locale object: ` EXEC sp_addlinkedserver Server_Remote; GO USE tempdb; GO CREATE SYNONYM MyEmployee FOR Server_Remote.AdventureWorks2012.HumanResources.Employee; GO`

Artemination Over a year ago

This could work, I just need a Strategy in the Department to switch beetween DB, and the best is that it will be invisible for the programmers.

Aaron Bertrand Over a year ago

It won't be invisible to the programmers. If you want one programmer to see Cust as DB1.dbo.Customer, and another to see Cust as DB2.dbo.Customer, how do you propose you do that at the same time? A synonym can only point to one object at a time, so it may work when you're the only developer, but beyond that you're out of luck...

|

Collectives™ on Stack Overflow

How to change my DB name in a query dynamically, but without exec a string?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related