CODEWITHSUNDEEP
sql-servert-sql
DanBot
DanBot

Reputation: 121

Get name of DB name from Dynamic SQL

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'

Upvotes: 0

Views: 96

Answers (1)

EzLo
EzLo

Reputation: 14189

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)

Upvotes: 3

Related Questions