Tagfinder, network-wide

1

Please login or register to vote for this query.

(click on this box to dismiss)

Shows all tags matching a substring, including synonyms by their relevance on all sites.

Stack Overflow

Q&A for programmers

DECLARE @dbTag TABLE (
  DbName VARCHAR(MAX),
  TagName VARCHAR(MAX),
  Count INT
)

DECLARE @dbQNum TABLE (
  DbName VARCHAR(MAX),
  QNum INT
)

DECLARE @dbName VARCHAR(MAX)
DECLARE @query VARCHAR(MAX)

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

OPEN dbCursor;
FETCH NEXT FROM dbcursor INTO @dbName;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
    WITH Raw AS (
      SELECT TagName, Count
      FROM [' + @dbName + '].dbo.Tags

      UNION

      SELECT TagSynonyms.SourceTagName AS TagName, Tags.Count
      FROM [' + @dbName + '].dbo.Tags, [' + @dbName + '].dbo.TagSynonyms
      WHERE Tags.TagName = TagSynonyms.SourceTagName
    )
    SELECT ''' + @dbName + ''' AS DbName, TagName, SUM(Count) AS Count
    FROM Raw
    GROUP BY TagName
  ';
  INSERT INTO @dbTag EXEC(@query);
  
  SELECT @query = '
    SELECT 
      ''' + @dbName + ''' AS DbName,
      COUNT(*) AS QNum
    FROM [' + @dbName + '].dbo.Posts
    WHERE PostTypeId = 1
  ';
  INSERT INTO @dbQNum EXEC(@query);

  FETCH NEXT FROM dbCursor INTO @dbName;
END

CLOSE dbCursor;

WITH SiteToDb 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://' + SiteToDb.SiteName + '|' + SiteToDb.SiteName AS Site,
  'https://' + SiteToDb.SiteName + '/questions/tagged/' + dbTag.TagName
    + '|' + dbTag.TagName AS Tag,
  100.0 * dbTag.Count / dbQNum.QNum AS PctOfQuestions,
  dbTag.Count
FROM @dbTag dbTag, @dbQNum dbQNum, SiteToDb
WHERE
  SiteToDb.DbName = dbTag.DbName
  AND
  dbTag.DbName = dbQNum.DbName
  AND
  dbTag.TagName LIKE '%' + ##tagFilter:string## + '%'
ORDER BY PctOfQuestions DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results