Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs
-- result table, don't rename and keep the site column CREATE TABLE #results (site nvarchar(250), postid int, score int, tags nvarchar(250), creationdate datetime, title nvarchar(250)); 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 ALL' , 'INSERT INTO #results' ) + -- here goes the per site query, fully qualify the database objects N' SELECT ''' + name + ''', Id, Score, Tags, CreationDate, Title FROM ' + quotename(name) + '.dbo.Posts WHERE Tags LIKE ''%<faq>%'' ' FROM sys.databases WHERE name LIKE '%.Meta' -- execute it EXEC (@sql) SELECT site, LOWER('https://' + IIF(PATINDEX('%.Mathoverflow%', site) > 0, IIF(PATINDEX('%.Meta', site) > 0, 'meta.mathoverflow.net', 'mathoverflow.net'), IIF(PATINDEX('%.Ubuntu%', site) > 0, IIF(PATINDEX('%.Meta', site) > 0, 'meta.askubuntu.com', 'askubuntu.com'), IIF(PATINDEX('StackExchange.%', site) > 0, CASE SUBSTRING(site, 15, 200) WHEN 'Audio' THEN 'video' WHEN 'Audio.Meta' THEN 'video.meta' WHEN 'Beer' THEN 'alcohol' WHEN 'Beer.Meta' THEN 'alcohol.meta' WHEN 'CogSci' THEN 'psychology' WHEN 'CogSci.Meta' THEN 'psychology.meta' WHEN 'Garage' THEN 'mechanics' WHEN 'Garage.Meta' THEN 'mechanics.meta' WHEN 'Moderators' THEN 'communitybuilding' WHEN 'Moderators.Meta' THEN 'communitybuilding.meta' WHEN 'Photography' THEN 'photo' WHEN 'Photography.Meta' THEN 'photo.meta' WHEN 'Programmers' THEN 'softwareengineering' WHEN 'Programmers.Meta' THEN 'softwareengineering.meta' WHEN 'Vegetarian' THEN 'vegetarianism' WHEN 'Vegetarian.Meta' THEN 'vegetarianism.meta' WHEN 'Writers' THEN 'writing' WHEN 'Writers.Meta' THEN 'writing.meta' ELSE SUBSTRING(site, 15, 200) END + '.stackexchange.com', IIF(PATINDEX('StackOverflow.%', site) > 0, CASE SUBSTRING(site, 15, 200) WHEN 'Br' THEN 'pt' WHEN 'Br.Meta' THEN 'pt.meta' ELSE SUBSTRING(site, 15, 200) END + '.stackoverflow.com', IIF(PATINDEX('%.Meta', site) > 0, 'meta.' + SUBSTRING(site, 0, PATINDEX('%.Meta', site)) + '.com', site + '.com' ) ) ) ) )) + '/q/' + CAST(postid AS NVARCHAR) + '|' + title, score, tags, creationdate FROM #results ORDER BY site, score DESC; DROP TABLE #results