Search the Stack Exchange network for tags

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- result table, don't rename and keep the site column
CREATE TABLE #results (site NVARCHAR(250),
                       tagname NVARCHAR(50),
                       timesused INT);

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 + ''', t.TagName, t.Count
  FROM ' + quotename(name) + '.dbo.Tags AS t
  WHERE t.TagName LIKE ''%' + ##query:string## + '%''
  
UNION ALL

SELECT ''' + name + ''', s.SourceTagName, t.Count
  FROM ' + quotename(name) + '.dbo.Tags AS t
  INNER JOIN ' + quotename(name) + '.dbo.TagSynonyms AS s
    ON t.TagName = s.TargetTagName
  WHERE s.SourceTagName LIKE ''%' + ##query:string## + '%''

'
FROM sys.databases
WHERE database_id > 5
  AND name NOT LIKE 'StackExchange.Arabic%'
  AND (name NOT LIKE '%.Meta' OR name = 'StackExchange.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'
            )
          )
        )
      )
      )) + '/tags/' + tagname + '/info|' + tagname,
      timesused AS 'Times used'
  FROM #results
  ORDER BY timesused DESC, site
     
DROP TABLE #results

Enter Parameters

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