Tags (networkwide comparison)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

declare @tagname nvarchar(35) = ##tagname:string##

-- result table, don't rename and keep the site column
create table #results (site nvarchar(250)
                     , id int
                     , tagname nvarchar(35)
                     , body nvarchar(max));

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.id
     , t.tagname
     , p.body
from ' + quotename(name) + '.dbo.tags as t
left outer join ' + quotename(name) + '.dbo.posts as p
  on t.excerptpostid = p.id
where tagname = ''' + @tagname + ''' '
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

-- 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 + '/info|'
      + replace(site, 'stackexchange.','') AS 'Site'
      -- rest of columns
      , body AS 'Excerpt'
  from #results
  order by site

drop table #results

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results