Q&A about the site for developers and users of the secure, private and untraceable cryptocurrency Monero
CREATE TABLE #Sites ( DbId INT NOT NULL, Type BIT NOT NULL, Count 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' --,'StackOverflow' ); 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, 0 AS Type, COUNT(*) FROM [' + @DbName + '].dbo.PostsWithDeleted WHERE PostTypeId = 1 AND CreationDate > ''2018-01-01'' UNION ALL SELECT ' + CAST(@DbId AS VARCHAR(MAX)) + ' AS DbId, 1 AS Type, COUNT(*) FROM [' + @DbName + '].dbo.Posts Q, [' + @DbName + '].dbo.Posts A WHERE Q.AcceptedAnswerId = A.Id AND Q.Score > 0 AND A.Score > 0 AND Q.ClosedDate IS NULL AND Q.CreationDate > ''2018-01-01'' '; FETCH NEXT FROM dbcursor INTO @DbId, @DbName; END CLOSE dbcursor; PRINT @query; INSERT INTO #Sites EXEC (@query); SELECT sysdatabases.name AS DbName, W.Count AS WellQ, T.Count AS TotalQ, 100.0 * W.Count / T.Count AS Pct FROM #Sites T, #Sites W, sysdatabases WHERE T.DbId = W.DbId AND T.Type = 0 AND W.Type = 1 AND T.DbId = sysdatabases.dbid ORDER BY Pct DESC, T.DbId