Q&A about the site for musicians, students, and 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 [Date] DATE, [NewQuestions] INT -- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ) 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 DATEFROMPARTS(YEAR(P.CreationDate), MONTH(P.CreationDate), 1) AS Date, COUNT(*) AS NewQuestions FROM Posts P WHERE P.PostTypeId = 1 -- Questions AND YEAR(P.CreationDate) >= 2021 GROUP BY DATEFROMPARTS(YEAR(P.CreationDate), MONTH(P.CreationDate), 1) ' -- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 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 MAX([Date]) AS Date, SUM([NewQuestions]) AS NewQuestions FROM #AllSiteResults GROUP BY [Date] ORDER BY [Date]