Variable which replace DB of name in SSMS

Question

I have two DB (DB1 & DB2) on the same server (instance) and I know i can write query like this:

select d1.name, d2.email
from [db1].[dbo].[person] as d1
join [db2].[dbo].[details_person] as d2
on d1.id = d2.id_p

but my question is: can i create something like variable which allows replace [db2] in my query when my database will change name.

I mean code similar like this: [$(var_db2)].[dbo].[details_person] but i don't know if i can create variable in SSMS which will be referes to database in the same server and if it is possible at all.

Possible duplicate of [Table name as variable](http://stackoverflow.com/questions/2838490/table-name-as-variable) — Lukasz Szozda, Dec 14 '15 at 08:14
TableName/DB Name/Schema are the same case. You can use Dynamic SQL. Remember to use `QUOTENAME` to avoid SQL Injection. — Lukasz Szozda, Dec 14 '15 at 08:14

Pரதீப் · Accepted Answer · 2015-12-14T08:37:00.777

Use Dynamic Sql to set DB dynamically. Try something like this

declare @char varchar(10) = 'db_name'

exec 
(
'use '+Quotename(@char) + '
select d1.name, d2.email
from [dbo].[person] as d1
join [dbo].[details_person] as d2
on d1.id = d2.id_p'
)

or

DECLARE @sql NVARCHAR(max)
DECLARE @char VARCHAR(10) = 'db_rd'

SET @sql = 'select d1.name, d2.email
from ' + Quotename(@char) + '.[dbo].[person] as d1
join '+ Quotename(@char) + '.[dbo].[details_person] as d2
on d1.id = d2.id_p'

EXEC Sp_executesql @sql

Variable which replace DB of name in SSMS

1 Answers1