My Profiles Across Stack Exchange

1

Please login or register to vote for this query.

(click on this box to dismiss)

Audio-Video Production Meta

Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation

/*
    set this to your AccountId from the StackOverflow database
    find that by running:
    
        SELECT AccountId 
        FROM dbo.Users u 
        WHERE u.DisplayName = N'xxx';
*/
DECLARE @AccountId int; 

SET @AccountId = ##AccountID:int?52354##;

CREATE TABLE #results
(
      DatabaseName sysname NOT NULL
    , Id int NOT NULL
    , Reputation int NOT NULL
    , CreationDate datetime NOT NULL
    , DisplayName nvarchar(1000) NULL
    , LastAccessDate datetime NULL
    , WebsiteUrl nvarchar(1000) NULL
    , Location nvarchar(1000) NULL
    , AboutMe nvarchar(max) NULL
    , Views int NOT NULL
    , UpVotes int NOT NULL
    , DownVotes int NOT NULL
    , ProfileImageUrl nvarchar(1000) NULL
    , EmailHash varchar(32) NULL
    , AccountId int NOT NULL
);

DECLARE @cmd nvarchar(max);
DECLARE @dbid int;
DECLARE @parameters nvarchar(max);
SET @parameters = N'@AccountIdInner int';
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR 
SELECT d.database_id
FROM sys.databases d
WHERE d.database_id > 4
ORDER BY d.name;
OPEN cur;
FETCH NEXT FROM cur INTO @dbid;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = N'
    DECLARE @parameters nvarchar(max);
    SET @parameters = N''@AccountIdInner int'';
    IF EXISTS (
        SELECT 1 
        FROM ' + QUOTENAME(DB_NAME(@dbid)) + N'.sys.tables t 
            INNER JOIN ' + QUOTENAME(DB_NAME(@dbid)) + N'.sys.columns c ON t.object_id = c.object_id 
        WHERE t.name = N''Users''
            AND c.name = N''DisplayName''
            )
    BEGIN
        DECLARE @cmd nvarchar(max);
        SET @cmd = N''
        SELECT [DatabaseName] = ''''' + DB_NAME(@dbid) + N'''''
            , u.*
            FROM ' + QUOTENAME(DB_NAME(@dbid)) + N'.dbo.Users u
        WHERE u.AccountId = @AccountIdInner
        ORDER BY u.Id;'';
        EXEC sys.sp_executesql @stmt = @cmd, @params = @parameters, @AccountIdInner = @AccountIdInner;
    END
    ';
    BEGIN TRY
        INSERT INTO #results
        EXEC sys.sp_executesql @stmt = @cmd, @params = @parameters, @AccountIdInner = @AccountId;
    END TRY
    BEGIN CATCH
        DECLARE @msg nvarchar(1000);
        SET @msg = ERROR_MESSAGE() + N' at ' + CONVERT(nvarchar(100), ERROR_LINE());
        PRINT @msg;
    END CATCH
    FETCH NEXT FROM cur INTO @dbid;
END
CLOSE cur;
DEALLOCATE cur;
SELECT *
FROM #results r
ORDER BY r.CreationDate;
    
    SELECT [Count of Accounts] = COUNT(1)
    , TotalRep = SUM(r.Reputation)
    , TotalProfileViews = SUM(Views)
    , TotalUpVotes = SUM(UpVotes)
    , TotalDownVotes = SUM(DownVotes)
FROM #results r
WHERE r.DatabaseName NOT LIKE N'%.Meta'
    AND r.Reputation <> 101 --exclude "Associaton Bonus" accounts from total calc;

Enter Parameters

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