1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Drupal developers and administrators

DECLARE @result TABLE (
  DbName VARCHAR(MAX) NOT NULL,
  AccountId INT NOT NULL,
  UserName VARCHAR(MAX) NOT NULL,
  SiteUserId INT NOT NULL,
  Reputation INT NOT NULL,
  CreationDate DATETIME NOT NULL,
  LastAccessDate DATETIME NOT NULL,
  ProfileImageUrl VARCHAR(MAX)
);

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
  IF @query <> ''
    SET @query = @query + '
    UNION ALL
    ';
    
  SELECT @query = @query + '
    SELECT
      ''' + @dbName + ''' AS DbName,
      AccountId,
      DisplayName AS UserName,
      Id AS SiteUserId,
      Reputation,
      CreationDate,
      LastAccessDate,
      ProfileImageUrl
    FROM [' + @dbName + '].dbo.Users
    WHERE AccountId IN (
      SELECT AccountId
      FROM [' + @dbName + '].dbo.Users
      WHERE AccountId > 0
      GROUP BY AccountId
      HAVING COUNT(*) > 1
    )
  ';

  FETCH NEXT FROM dbcursor INTO @DbName;
END

INSERT INTO @result EXEC(@query);

SELECT
  DbName,
  AccountId,
  'https://stackexchange.com/users/' + CAST(AccountId AS VARCHAR(MAX))
    + '?tab=accounts|' + UserName AS Profile,
  SiteUserId,
  Reputation,
  CreationDate,
  LastAccessDate,
  ProfileImageUrl
FROM @result
ORDER BY AccountId, DbName, SiteUserId

fork query

edited dec 12

created dec 11 peterh - PLEASE DELETE THIS ACCOUNT