1

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited apr 10 peterh - PLEASE DELETE THIS ACCOUNT

created aug 11 17 peterh