1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for designers and implementers of computer programming languages

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , tagname nvarchar(35)
                       , excerpt nvarchar(max)
                       , wiki nvarchar(max)
                       , tagcount 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 + '''
     , t.tagname
     , pe.body
     , pw.body
     , t.count
from ' + quotename(name) + '.dbo.tags t 
inner join ' + quotename(name) + '.dbo.posts pe on pe.id = t.excerptpostid
inner join ' + quotename(name) + '.dbo.posts pw on pw.id = t.wikipostid
'
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

--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'
      ) 
      + '/tags/'
      + tagname 
      + '|'
      + replace(site, 'stackexchange.','')
      -- rest of columns
      , tagname
      , tagcount
from #results
where tagcount < 57
order by tagcount desc

drop table #results

fork query

edited nov 16 19 Oliver Widder

created dec 8 16 rene