Sites by relative meta activity (compared to the main) (parallelizable version)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Role-playing Games

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

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

DECLARE @DbName VARCHAR(MAX);
DECLARE @Query VARCHAR(MAX) = '';
DECLARE @Output TABLE (
  Db VARCHAR(MAX),
  UserCount INT
);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @Query <> '' SELECT @Query = @Query + ' UNION ';
    
  SELECT @Query = @Query + '
    SELECT
      ''' + @DbName + ''' AS Db,
      COUNT(DISTINCT OwnerUserId)
    FROM [' + @DbName + '].dbo.Posts
    LEFT JOIN [' + @DbName + '].dbo.Users
      ON Posts.OwnerUserId = Users.Id
    WHERE Users.Reputation >= 
      ' + CAST(##RepLimit:int?301## AS VARCHAR(MAX)) + '
  ';
  FETCH NEXT FROM dbcursor INTO @DbName;
END;

INSERT INTO @output EXEC (@Query);

SELECT
  Main.Db AS MainDb,
  Main.UserCount AS MainUserCount,
  Meta.Db AS MetaDb,
  Meta.UserCount AS MetaUserCount,
  100.0 * Meta.UserCount / Main.UserCount AS MetaActivityPct
FROM @output Main, @output Meta
WHERE
  Main.Db + '.Meta' = Meta.Db
ORDER BY MetaActivityPct;

Enter Parameters

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