Q&A about the site for parents, grandparents, nannies and others with a parenting role
-- result table, don't rename and keep the site column create table #results ( site nvarchar(250) , id 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 + ''' , p.parentid from ' + quotename(name) + '.dbo.posts p inner join ' + quotename(name) + '.dbo.users u on p.owneruserid = u.id where u.accountid = ' + CAST(##AccountId:int## AS nvarchar) + ' and p.posttypeid = 2 group by p.parentid having count(*) > 1' 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' ) + '/q/' + cast(id as nvarchar) + '|' + replace(site, 'stackexchange.','') -- rest of columns , id from #results drop table #results