Bounty tracking, all sites

0

Please login or register to vote for this query.

(click on this box to dismiss)

Movies and TV Meta

Q&A about the site for movie and tv enthusiasts

/*-- INSTRUCTIONS:
    1)  Set the columns of #AllSiteResults to what you need in the final query.
    2)  Set the @seSiteQuery text (inside the WHILE loop) to the query that will run on each site to build
        the #AllSiteResults table.
    3)  Comment out the `WHERE       (dadn.dbName = 'StackExchange.Meta'...` line if site metas are desired.
    4)  Adjust the final query if post processing is desired (optional).
*/
DECLARE @seDbName       AS NVARCHAR (max)
DECLARE @seSiteURL      AS NVARCHAR (max)
DECLARE @sitePrettyName AS NVARCHAR (max)
DECLARE @seSiteQuery    AS NVARCHAR (max)

CREATE TABLE #AllSiteResults (
      -- PUT THE COLUMNS YOU WILL USE, HERE
      -- vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
      [SiteURL]            NVARCHAR(max)
      , [SitePrettyName]   NVARCHAR(max)
      , [PostId]    INT
      , [PostTitle]  NVARCHAR(max)
      , [PostDate]   DATE
      , [QuestionPosterId] INT
      , [QuestionPosterName] NVARCHAR(max)
      , [BountyPosterId] INT
      , [BountyPosterName]  NVARCHAR(max)
      -- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
)

DECLARE seSites_crsr CURSOR FOR
WITH dbsAndDomainNames AS (
    SELECT      dbL.dbName
                , STRING_AGG (dbL.domainPieces, '.')    AS siteDomain
    FROM (
        SELECT      TOP 50000   -- Never be that many sites and TOP is needed for order by, below
                    name        AS dbName
                    , value     AS domainPieces
                    , row_number ()  OVER (ORDER BY (SELECT 0)) AS [rowN]
        FROM        sys.databases
        CROSS APPLY STRING_SPLIT (name, '.')
        WHERE       CASE    WHEN state_desc = 'ONLINE'
                            THEN OBJECT_ID (QUOTENAME (name) + '.[dbo].[PostNotices]', 'U') -- Pick a table unique to SE data
                    END
                    IS NOT NULL
        ORDER BY    dbName, [rowN] DESC
    ) AS dbL
    GROUP BY    dbL.dbName
)
SELECT      REPLACE (REPLACE (dadn.dbName, 'StackExchange.', ''), '.', ' ' )  AS [Site Name]
            , dadn.dbName
            , CASE  -- See https://meta.stackexchange.com/q/215071
                    WHEN dadn.dbName = 'StackExchange.Mathoverflow.Meta'
                    THEN 'https://meta.mathoverflow.net/'
                    -- Some AVP/Audio/Video/Sound kerfuffle?
                    WHEN dadn.dbName = 'StackExchange.Audio'
                    THEN 'https://video.stackexchange.com/'
                    -- Ditto
                    WHEN dadn.dbName = 'StackExchange.Audio.Meta'
                    THEN 'https://video.meta.stackexchange.com/'
                    -- Normal site
                    ELSE 'https://' + LOWER (siteDomain) + '.com/'
            END AS siteURL
FROM        dbsAndDomainNames dadn
--WHERE       (dadn.dbName = 'StackExchange.Meta'  OR  dadn.dbName NOT LIKE '%Meta%')

-- Step through cursor
OPEN    seSites_crsr
FETCH   NEXT FROM seSites_crsr INTO @sitePrettyName, @seDbName, @seSiteURL
WHILE   @@FETCH_STATUS = 0
BEGIN
    -- QUERY THAT YOU WANT TO RUN ON EACH SITE, GOES HERE
    -- For example:
    -- vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
    SET @seSiteQuery = '
        USE [' + @seDbName + ']

        INSERT INTO #AllSiteResults
        SELECT
            ''' + @seSiteURL + ''' AS [SiteURL],
            ''' + @sitePrettyName + ''' AS [SitePrettyName],
            PostsWithDeleted.Id AS [PostId],
            PostsWithDeleted.Title AS [PostTitle],            
            PostsWithDeleted.CreationDate AS [PostDate],
            Users.Id AS [QuestionPosterId],
            Users.DisplayName AS [QuestionPosterName],
            PostNotices.OwnerUserId AS [BountyPosterId],
            PostUser.DisplayName AS [BountyPosterName]
        FROM PostsWithDeleted
          INNER JOIN Users ON PostsWithDeleted.OwnerUserId = Users.Id
          INNER JOIN PostNotices ON PostsWithDeleted.Id = PostNotices.PostId
          INNER JOIN PostNoticeTypes ON PostNotices.PostNoticeTypeId = PostNoticeTypes.Id
          LEFT JOIN Users AS PostUser ON PostNotices.OwnerUserId = PostUser.Id
        WHERE
          PostsWithDeleted.PostTypeId = 1
            AND PostNoticeTypes.ClassId = 2
            AND Users.Id <> PostNotices.OwnerUserId
            AND Users.AccountId IN (##UserIds##)
    '
    -- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    EXEC sp_executesql @seSiteQuery

    FETCH NEXT FROM seSites_crsr INTO @sitePrettyName, @seDbName, @seSiteURL
END
CLOSE       seSites_crsr
DEALLOCATE  seSites_crsr

-- ADJUST THIS QUERY IF ANY POST PROCESSING IS DESIRED.
SELECT
  SitePrettyName as Site,
  PostDate as 'Question Date',
  SiteURL
    + 'questions/'
    + CONVERT(varchar(15),PostId)
    + '|' + PostTitle
  AS 'Question',
  SiteURL
    + 'users/'
    + CONVERT(varchar(15),QuestionPosterId)
    + '|'
    + QuestionPosterName
  AS 'Question Poster',
  SiteURL
    + 'users/'
    + CONVERT(varchar(15),BountyPosterId)
    + '|'
    + (CASE
        WHEN BountyPosterName IS NULL
        THEN '[deleted:' + CONVERT(varchar(15),BountyPosterId) + ']'
        ELSE BountyPosterName
      END)
  AS 'Bounty Poster'
FROM        #AllSiteResults
ORDER BY    [PostDate] DESC

Enter Parameters

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