Sites by ratio of soft/hard deleted posts

1

Please login or register to vote for this query.

(click on this box to dismiss)

Motor Vehicle Maintenance and Repair Meta

Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

CREATE TABLE #Sites (
  DbId INT NOT NULL,
  DbName VARCHAR(MAX) NOT NULL,
  TotalPosts INT NOT NULL,
  SoftDeleted INT NOT NULL,
  HardDeleted 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
      ' + CAST(@DbId AS VARCHAR(MAX)) + ' AS DbId,
      ''' + @DbName + ''' AS DbName,
      (
        SELECT MAX(Id)
        FROM [' + @DbName + '].dbo.PostsWithDeleted
      ) AS TotalPosts,
      (
        SELECT COUNT(*)
        FROM [' + @DbName + '].dbo.PostsWithDeleted
        WHERE DeletionDate IS NOT NULL
      )
      AS SoftDeleted,
      (
        SELECT MAX(Id) - COUNT(*)
        FROM [' + @DbName + '].dbo.PostsWithDeleted
      )
      AS HardDeleted
  ';

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

INSERT INTO #Sites EXEC(@query);

SELECT
  DbId,
  DbName,
  TotalPosts,
  SoftDeleted,
  100.0 * SoftDeleted / TotalPosts AS SoftDeletedPct,
  100.0 * SoftDeleted / (TotalPosts - HardDeleted) AS DbDeletedPct,
  HardDeleted,
  100.0 * HardDeleted / TotalPosts AS HardDeletedPct
FROM #Sites
ORDER BY SoftDeletedPct DESC, DbId;

Enter Parameters

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