Q&A for professional and enthusiast programmers
declare @tagname nvarchar(35) = 'mac' -- result table, don't rename and keep the site column create table #results ( site nvarchar(250) , tagname nvarchar(35) , 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 , t.count from ' + quotename(name) + '.dbo.tags t where t.tagname = ''' + @tagname + ''' collate sql_latin1_general_cp1_ci_ai ' 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 order by tagcount DESC drop table #results