Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs
DECLARE @tag TABLE ( DbName VARCHAR(MAX) NOT NULL, SynonymSource VARCHAR(MAX), SynonymTarget VARCHAR(MAX), TagName VARCHAR(MAX), Count INT NOT NULL, Excerpt VARCHAR(MAX), Wiki VARCHAR(MAX) ); DECLARE @query VARCHAR(MAX); DECLARE dbcursor CURSOR FOR SELECT name FROM sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange') AND name NOT LIKE '%Temp%'; DECLARE @dbname VARCHAR(MAX); DECLARE @tagName VARCHAR(MAX) = ##tagName:string## OPEN dbcursor; FETCH NEXT FROM dbcursor INTO @dbname; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @query = ' SELECT ''' + @dbname + ''' AS DbName, SourceTagName AS SynonymSource, TargetTagName AS SynonymTarget, Tags.TagName, Tags.Count, Excerpts.Body AS Excerpt, Wikis.Body AS Wiki FROM [' + @dbName + '].dbo.Tags LEFT JOIN [' + @dbName + '].dbo.TagSynonyms ON Tags.TagName = TagSynonyms.TargetTagName LEFT JOIN [' + @dbName + '].dbo.Posts Excerpts ON Tags.ExcerptPostId = Excerpts.Id LEFT JOIN [' + @dbName + '].dbo.Posts Wikis ON Tags.WikiPostId = Wikis.Id WHERE TagSynonyms.SourceTagName = ''' + @tagName + ''' OR TagSynonyms.TargetTagName = ''' + @tagName + ''' OR Tags.TagName = ''' + @tagName + ''' '; INSERT INTO @tag EXEC (@query); FETCH NEXT FROM dbcursor INTO @dbname; END CLOSE dbcursor; WITH Site2Db AS ( SELECT name AS DbName, LOWER(CASE WHEN name = 'StackExchange.Programmers' THEN 'softwareengineering.stackexchange.com' WHEN name = 'StackExchange.Programmers.Meta' THEN 'softwareengineering.meta.stackexchange.com' WHEN name LIKE 'StackExchange.%' THEN RIGHT(name, LEN(name) - 14) + '.stackexchange.com' WHEN name = 'StackOverflow.Br' THEN 'pt.stackoverflow.com' WHEN name = 'StackOverflow.Br.Meta' THEN 'pt.meta.stackoverflow.com' WHEN name LIKE 'StackOverflow.__' THEN RIGHT(name, 2) + '.stackoverflow.com' WHEN name LIKE 'StackOverflow.__.Meta' THEN RIGHT(name, 7) + '.stackoverflow.com' WHEN name = 'StackOverflow' THEN 'stackoverflow.com' WHEN name = 'StackOverflow.Meta' THEN 'meta.stackoverflow.com' WHEN name = 'StackApps' THEN 'stackapps.com' WHEN name = 'ServerFault' THEN 'serverfault.com' WHEN name = 'ServerFault.Meta' THEN 'meta.serverfault.com' WHEN name = 'SuperUser' THEN 'superuser.com' WHEN name = 'SuperUser.Meta' THEN 'meta.superuser.com' ELSE NULL END ) AS SiteName FROM sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange') ) SELECT Site2Db.SiteName, 'https://' + Site2Db.SiteName + '/tags/' + tag.TagName + '|' + tag.TagName AS [Tag Link], tag.SynonymSource, tag.SynonymTarget, tag.Count, tag.Excerpt, tag.Wiki FROM @tag tag, Site2Db WHERE Site2Db.DbName = tag.DbName ORDER BY tag.Count DESC, Site2Db.SiteName