List of sites by ratio of posts / VoteType4

1

Please login or register to vote for this query.

(click on this box to dismiss)

Blender Meta

Q&A about the site for people who use Blender to create 3D graphics, animations, or games

CREATE TABLE #Result (
  DbName VARCHAR(MAX) NOT NULL,
  Posts INT NOT NULL,
  Votes 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
      ''' + @DbName + ''' AS DbName,
      (
        SELECT COUNT(*)
        FROM [' + @DbName + '].dbo.PostsWithDeleted
      ) AS Posts,
      (
        SELECT COUNT(*)
        FROM [' + @DbName + '].dbo.Votes
        WHERE VoteTypeId = ' + CAST(##VoteTypeId:int?4## AS VARCHAR(MAX))
        + '
      ) AS Votes
  ';

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

INSERT INTO #Result EXEC (@query);

SELECT
  DbName,
  Posts,
  Votes,
  CASE
    WHEN  Votes > 0 THEN 1.0 * Posts / Votes
  END AS Ratio
FROM #Result
ORDER BY Ratio DESC;

Enter Parameters

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