Multiple site accounts to the same network account, network-wide

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Apps

Q&A for apps, scripts, and development with the Stack Exchange API

CREATE TABLE #Accounts (
  DbName VARCHAR(MAX) NOT NULL,
  SiteUserId INT NOT NULL,
  UserName VARCHAR(MAX) NOT NULL,
  NetworkUserId INT NOT NULL,
  CreationDate DATETIME NOT NULL,
  LastAccessDate DATETIME NOT NULL,
  Reputation INT NOT NULL,
  Count INT NOT NULL
);

DECLARE @query VARCHAR(MAX) = '';

DECLARE dbcursor CURSOR FOR
SELECT dbid, name
FROM sysdatabases
WHERE
  name NOT IN (
    'master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange'
  );

DECLARE @DbName VARCHAR(MAX);
DECLARE @DbId INT;


OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @DbId, @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @query <> ''
    SET @query = @query + '
    UNION ALL
    ';

  SELECT @query = @query + '
    SELECT *
    FROM (
      SELECT
        ''' + @DbName + ''' AS DbName,
        Id AS SiteUserId,
        DisplayName AS UserName,
        AccountId AS NetworkUserId,
        CreationDate As CreationDate,
        LastAccessDate,
        Reputation,
        COUNT(*) OVER (PARTITION BY AccountId) AS Count
      FROM [' + @DbName + '].dbo.Users
      WHERE AccountId IS NOT NULL) p
    WHERE
      Count > 1
  ';

  FETCH NEXT FROM dbcursor INTO @DbId, @DbName;
END

INSERT INTO #Accounts EXEC (@query);

SELECT
  DbName,
  'https://stackexchange.com/users/' + CAST(NetworkUserId AS VARCHAR(MAX))
    + '?tab=accounts|' + CAST(NetworkUserId AS VARCHAR(MAX))
    AS [Accounts Link],
  'https://data.stackexchange.com/stackoverflow/query/1089480'
    + '?NetworkAccountId=' + CAST(NetworkUserId AS VARCHAR(MAX))
    + '|' + CAST(NetworkUserId AS VARCHAR(MAX))
    AS [With Hidden Accounts],
  Count,
  SiteUserId,
  NetworkUserId,
  UserName,
  CreationDate,
  LastAccessDate,
  Reputation
FROM #Accounts
ORDER BY NetworkUserId DESC, DbName, SiteUserId

Enter Parameters

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