0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

CREATE TABLE #results (site NVARCHAR(250),
                       badge1count INT,
                       badge2count INT);
                       
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql 
+ IIF( LEN(@sql) > 1 
     , 'UNION ALL'
     , 'INSERT INTO #results'
) +
N'
SELECT ''' + name + ''',
  (SELECT COUNT(*) FROM ' + QUOTENAME(name) + '.dbo.Badges
     WHERE Name = ''' + ##BadgeName1:string## + '''),
  (SELECT COUNT(*) FROM ' + QUOTENAME(name) + '.dbo.Badges
     WHERE Name = ''' + ##BadgeName2:string## + ''')
'
FROM sys.databases
WHERE database_id > 5
  AND name NOT LIKE '%.Meta' OR name = 'StackExchange.Meta'

-- execute it
EXEC (@sql)

SELECT site,
  badge1count AS ##BadgeName1:string##,
  badge2count AS ##BadgeName2:string##,
  CASE WHEN badge2count = 0 THEN NULL
       ELSE ROUND(badge1count / CAST(badge2count AS FLOAT), 2)
  END AS 'Ratio'
  FROM #results
  ORDER BY 4 DESC;
     
DROP TABLE #results

fork query

edited nov 6 19

created nov 6 19 Glorfindel