Q&A for users and developers of the Cardano cryptocurrency ecosystem
-- 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