Q&A for professional and enthusiast programmers
CREATE TABLE #output ( dbname VARCHAR(max), UserName NVARCHAR(40), SEAccountId INT, HnqCount INT ); DECLARE @query VARCHAR(MAX); DECLARE dbcursor CURSOR FOR SELECT name FROM sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange'); DECLARE @dbname VARCHAR(max); OPEN dbcursor; FETCH NEXT FROM dbcursor INTO @dbname; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @query = ' SELECT ''' + @dbname + ''' AS dbname, MIN(Users.DisplayName) AS UserName, Users.AccountId AS SEAccountId, COUNT(*) AS HnqCount FROM [' + @dbname + '].dbo.Users, [' + @dbname + '].dbo.Posts, [' + @dbname + '].dbo.PostHistory WHERE PostHistory.PostHistoryTypeId = 52 AND PostHistory.PostId = Posts.Id AND Posts.OwnerUserId = Users.Id GROUP BY Users.AccountId '; INSERT INTO #output EXEC (@query); FETCH NEXT FROM dbcursor INTO @dbname; END CLOSE dbcursor; WITH Raw AS ( SELECT SUM(HnqCount) AS HnqCount, MIN(UserName) AS UserName, SEAccountId FROM #output GROUP BY SEAccountId ) SELECT DENSE_RANK() OVER (ORDER BY HnqCount DESC) AS Rank, UserName, SEAccountId, HnqCount, 'http://stackexchange.com/users/' + CAST(SEAccountId AS VARCHAR(30)) + '?tab=accounts|' + UserName AS 'SE Profile', 'https://data.stackexchange.com/stackoverflow/query/678540?AccountId=' + CAST(SEAccountId AS VARCHAR(30)) + '#resultSets|' + UserName AS [With Hidden Accounts] FROM Raw ORDER BY HnqCount DESC, SEAccountId;