0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- result table, don't rename and keep the site column
create table #results (site nvarchar(250), type nvarchar(10), id int, title nvarchar(250),
  creationdate datetime, score int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , ''Answer'', a.id, q.title, a.creationdate, a.score
from ' + quotename(name) + '.dbo.posts as a
inner join ' + quotename(name) + '.dbo.posts as q on a.parentid = q.id
inner join ' + quotename(name) + '.dbo.users as u on a.owneruserid = u.id
where u.accountid = ' + CAST(##AccountId:int## AS nvarchar) + '
  and a.body LIKE ''%' + ##keyword:string## + '%'''
  +
'union' +
N'
select ''' + name + '''
     , ''Question'', q.id, q.title, q.creationdate, q.score
from ' + quotename(name) + '.dbo.posts as q
inner join ' + quotename(name) + '.dbo.users as u on q.owneruserid = u.id
where u.accountid = ' + CAST(##AccountId:int## AS nvarchar) + '
  and q.posttypeid = 1
  and q.body LIKE ''%' + ##keyword:string## + '%'''
from sys.databases
where database_id > 5

--print @sql

-- execute it
exec (@sql)




-- show results
select
       -- URL builder from Lamak http://stackoverflow.com/a/38129701
      'http://' 
      + case PARSENAME(site,1)
        when 'audio' then 'avp'
        else PARSENAME(site,1)
        end 
      + coalesce('.' 
        + case PARSENAME(site,2)
          when 'audio' then 'avp'
          else PARSENAME(site,2)
          end , '')
      + IIF(patindex('%.MathOverflow%',site) > 0
      , ''
      , coalesce('.' + PARSENAME(site,3), '')
      )
      + IIF(patindex('%.MathOverflow%',site) > 0
      , '.net'
      , '.com'
      ) 
      + (CASE type WHEN 'Answer' THEN '/a/' ELSE '/q/' END)
      + CAST(id AS nvarchar)
      + '|'
      + title,
      type,
      creationdate,
      score
from #results
order by score desc

drop table #results

view current version

edited dec 26 17

created dec 26 17 Glorfindel