1

Please login or register to vote for this query.

(click on this box to dismiss)

Allows also SQL patterns (%,_).

Q&A about the site for people who use Blender to create 3D graphics, animations, or games

CREATE TABLE #Tags (
  DbName VARCHAR(MAX) NOT NULL,
  TagId INT NOT NULL,
  TagName VARCHAR(MAX) NOT NULL,
  Excerpt VARCHAR(MAX),
  Wiki VARCHAR(MAX),
  Count INT NOT NULL
);

DECLARE @query VARCHAR(MAX) = '';

DECLARE dbcursor CURSOR FOR
SELECT dbid, name
FROM sysdatabases
WHERE
  name NOT IN (
    'master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange'
  );

DECLARE @DbName VARCHAR(MAX);
DECLARE @DbId INT;

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @DbId, @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @query <> ''
    SET @query = @query + '
    UNION ALL
    ';

  SELECT @query = @query + '
    SELECT
      ''' + @DbName + ''' AS DbName,
      Tags.Id AS TagId,
      Tags.TagName,
      E.Body AS Excerpt,
      W.Body AS Wiki,
      Tags.Count
    FROM
      [' + @DbName + '].dbo.Tags
    LEFT JOIN [' + @DbName + '].dbo.Posts E ON Tags.ExcerptPostId = E.Id
    LEFT JOIN [' + @DbName + '].dbo.Posts W ON Tags.WikiPostId = W.Id
    WHERE
      Tags.TagName LIKE ''' + ##TagPattern:string## + '''
      AND (
        E.Body IS NOT NULL OR W.Body IS NOT NULL
      )
  ';

  FETCH NEXT FROM dbcursor INTO @DbId, @DbName;
END

INSERT INTO #Tags EXEC (@query);

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
  'https://' +Site2Db.SiteName + '/tags/' + #Tags.TagName + '/info'
    AS TagUrl,
  #Tags.* 
FROM #Tags, Site2Db
WHERE
  #Tags.DbName = Site2Db.DbName
ORDER BY Count DESC, DbName;

fork query

edited may 15

created may 5 peterh - PLEASE DELETE THIS ACCOUNT