1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for amateur radio enthusiasts

-- @Gilles is simply... extreme

DECLARE @output TABLE (
  dbname VARCHAR(max),
  DisplayName NVARCHAR(40),
  AccountId INT);

DECLARE @query VARCHAR(MAX);

DECLARE dbcursor CURSOR FOR
SELECT name
FROM sysdatabases
WHERE (name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange')
  AND NOT name LIKE '%.Meta') OR name = 'StackExchange.Meta';

DECLARE @dbname VARCHAR(max);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
    SELECT ''' + @dbname + ''' AS dbname,
      DisplayName,
      AccountId AS result
    FROM [' + @dbname + '].dbo.Users
    WHERE Reputation > ' + CAST(##Reputation:INT?3000## AS VARCHAR(20)) + ';';
  INSERT INTO @output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

SELECT
  COUNT(*) AS 'Number of Accounts',
  'http://stackexchange.com/users/'
    + CAST(AccountId AS VARCHAR(30))
    + '?tab=accounts|' + MIN(DisplayName) AS 'SE Accounts link'
FROM @output
GROUP BY AccountId
ORDER BY 'Number of Accounts' DESC, 'SE Accounts link';

fork query

edited mar 31 17

created mar 31 17 peterh