Get name of DB name from Dynamic SQL

Question

I have two Servers, both containing multiple databases with the same tables.

I create a temp table with two columns: ServerName and DBName. Using that I am able to use the below SQL to get all the rows back I need.

However I need a column to show which databases each row has been returned from.

Probably a really simple way to do this which is eluding me.

Have tried using db_name() - gives me name of db being executed from. Tried using my temptable, but as nothing to join on I get a cartesian product.

set @SQL = STUFF((SELECT   '

UNION ALL

 ' + 'SELECT terminalname,path,SID as tillnumber, AlternateName , CASE WHEN 
 thyronremoteport = 29000 then ''Wired'' WHEN thyronremoteport = 25000 then 
''Wireless'' else ''Unknown'' end as ped

 FROM ' + quotename(SERVERNAME)  + '.' + quotename (dbname) +        
'.dbo.TerminalStats where LEN(terminalname) = 5 and substring(terminalname, 
 1,1) = ''T'''

FROM  #tempDBNames 

FOR XML PATH(''), type).value('.','varchar(max)'),1,15,'')

print (@SQL)

execute(@SQL)

So the result of the above query is the below dynamically created SQL which continues as per the below for about 20 databases.

SELECT terminalname,path,SID as tillnumber, AlternateName , CASE WHEN 
thyronremoteport = 29000 then 'Wired' WHEN thyronremoteport = 25000 then 
'Wireless' else 'Unknown' end as ped

 FROM [Server1].[db1].dbo.TerminalStats where LEN(terminalname) = 5 
 and substring(terminalname, 1,1) = 'T'

 UNION ALL

SELECT terminalname,path,SID as tillnumber, AlternateName , CASE WHEN 
thyronremoteport = 29000 then 'Wired' WHEN thyronremoteport = 25000 then 
'Wireless' else 'Unknown' end as ped

 FROM [Server2].[db1].dbo.TerminalStats where LEN(terminalname) = 5 and 
 substring(terminalname, 1,1) = 'T'

EzLo · Accepted Answer · 2019-02-11 10:31:44Z

3

Add the DB_NAME() result on the SELECT list.

set @SQL = STUFF((SELECT   '

UNION ALL

 ' + 'SELECT 
    terminalname,
    path,SID as tillnumber, 
    AlternateName , 
    DB_NAME() as DatabaseName,
    CASE WHEN thyronremoteport = 29000 then ''Wired'' WHEN thyronremoteport = 25000 then ''Wireless'' else ''Unknown'' end as ped

 FROM ' + quotename(SERVERNAME)  + '.' + quotename (dbname) +        
'.dbo.TerminalStats where LEN(terminalname) = 5 and substring(terminalname, 
 1,1) = ''T'''

FROM  #tempDBNames 

FOR XML PATH(''), type).value('.','varchar(max)'),1,15,'')

print (@SQL)

execute(@SQL)

Or just repeat the name from your table as literal.

set @SQL = STUFF((SELECT   '

UNION ALL

 ' + 'SELECT 
    terminalname,
    path,SID as tillnumber, 
    AlternateName , 
    ''' + quotename (dbname) + ''' As DatabaseName,
    CASE WHEN thyronremoteport = 29000 then ''Wired'' WHEN thyronremoteport = 25000 then ''Wireless'' else ''Unknown'' end as ped

 FROM ' + quotename(SERVERNAME)  + '.' + quotename (dbname) +        
'.dbo.TerminalStats where LEN(terminalname) = 5 and substring(terminalname, 
 1,1) = ''T'''

FROM  #tempDBNames 

FOR XML PATH(''), type).value('.','varchar(max)'),1,15,'')

print (@SQL)

execute(@SQL)

answered Feb 11, 2019 at 10:31

EzLo

14.2k10 gold badges38 silver badges40 bronze badges

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

3 Comments

Peter B Over a year ago

I was going to post an answer like the 2nd code block of this answer, so I think that would solve it.

DanBot Over a year ago

Thanks, I tried the first solution already, just getting master for all rows.However your second solution worked a treat, think I tried something similar, but had a typo or something stupid! Cheers.

EzLo Over a year ago

@user3811820 DB_NAME() will return the currently connected database for the session. If you connect to a database and query another then it won't work. You should use the 2nd option then.

Collectives™ on Stack Overflow

Get name of DB name from Dynamic SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related