Network-wide migration statistics

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 @migDist TABLE (
  DbName VARCHAR(MAX) NOT NULL,
  CommentSite VARCHAR(MAX) NOT NULL,
  PostHistoryTypeId INT NOT NULL,
  Count INT NOT NULL
);

DECLARE @query VARCHAR(MAX);

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

DECLARE @dbname VARCHAR(MAX);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
    WITH Step1 AS (
      SELECT
        RIGHT(Comment, LEN(Comment) - PATINDEX(''%://%'', Comment) - 2)
          AS Comment1,
        PostHistoryTypeId
      FROM [' + @dbName + '].dbo.PostHistory
      WHERE PostHistoryTypeId IN (35, 36)
    ),
    Step2 AS (
      SELECT
        LEFT(Comment1, PATINDEX(''%/%'', Comment1) - 1) AS CommentSite,
        PostHistoryTypeId
      FROM Step1
    )
    SELECT
      ''' + @dbname + ''' AS DbName,
      CommentSite,
      PostHistoryTypeId,
      COUNT(*) AS Count
    FROM Step2
    GROUP BY CommentSite, PostHistoryTypeId
  ';
  INSERT INTO @migDist EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

WITH SiteToDb AS (
  SELECT
    name AS DbName,
    LOWER(CASE
      WHEN
        name = 'StackExchange.Programmers'
      THEN
        'softwareeengineering.stackexchange.com'
      WHEN
        name = 'StackExchange.Programmers.Meta'
      THEN
        'softwareengineering.meta.stackexchange.com'
      WHEN
        name LIKE 'StackExchange.%'
      THEN
        RIGHT(name, LEN(name) - 14) + '.stackexchange.com'
      WHEN
        name = 'StackOverflow.Br'
      THEN
        'pt.stackoverflow.com'
      WHEN
        name = 'StackOverflow.Br.Meta'
      THEN
        'pt.meta.stackoverflow.com'
      WHEN
        name LIKE 'StackOverflow.__'
      THEN
        RIGHT(name, 2) + '.stackoverflow.com'
      WHEN
        name LIKE 'StackOverflow.__.Meta'
      THEN
        RIGHT(name, 7) + '.stackoverflow.com'
      WHEN
        name = 'StackOverflow'
      THEN
        'stackoverflow.com'
      WHEN
        name = 'StackOverflow.Meta'
      THEN
        'meta.stackoverflow.com'
      WHEN
        name = 'StackApps'
      THEN
        'stackapps.com'
      WHEN
        name = 'ServerFault'
      THEN
        'serverfault.com'
      WHEN
        name = 'ServerFault.Meta'
      THEN
        'meta.serverfault.com'
      WHEN
        name = 'SuperUser'
      THEN
        'superuser.com'
      WHEN
        name = 'SuperUser.Meta'
      THEN
        'meta.superuser.com'
      ELSE
        NULL
      END
    ) AS SiteName
  FROM sysdatabases
  WHERE
    name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange')
),
migDist AS (
  SELECT
    DbName,
    CommentSite,
    SUM(CASE WHEN PostHistoryTypeId = 36 THEN Count ELSE 0 END) AS 'Accepted',
    SUM(CASE WHEN PostHistoryTypeId = 35 THEN Count ELSE 0 END) AS 'Rejected'
  FROM @migDist
  GROUP BY DbName, CommentSite
),
Result AS (
  SELECT
    migDist.CommentSite AS SiteFrom,
    COALESCE(SiteToDb.SiteName, migDist.DbName) AS SiteTo,
    Accepted + Rejected AS [All],
    Accepted,
    Rejected
  FROM migDist
  LEFT JOIN SiteToDb ON migDist.DbName = SiteToDb.DbName
)
SELECT *
FROM Result
WHERE
  (
  ##SiteFrom:string?-## = '-'
  OR
  LOWER(SiteFrom) LIKE '%' + LOWER(##SiteFrom##) + '%'
  )
  AND
  (
  ##SiteTo:string?-## = '-'
  OR
  LOWER(SiteTo) LIKE '%' + LOWER(##SiteTo##) + '%'
  )
ORDER BY Accepted DESC, Rejected DESC, SiteFrom, SiteTo;

Enter Parameters

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