0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- all databases
declare  db_c cursor for select [name] 
                         from sys.databases 
                         where database_id > 95
declare @db_c_name sysname   -- holds name of db after fetch
declare @sql nvarchar(max) -- holds build up sql string

-- result table
create table #all_users ( site nvarchar(250),
                           [Total Question Veteran] int,
                           [Unanswered Questions Veteran] int,
                           [Unanswered Percentage Veteran] float, 
                           [Uncommented Questions Veteran] int,
                           [Uncommented Percentage Veteran] float,
                           [Unengaged Questions Veteran] int, 
                           [Unengaged Percentage Veteran] float
                            );


open db_c
--fetch next from db_c into @db_c_name
while(@@FETCH_STATUS = 0)
begin
    fetch next from db_c into @db_c_name
    if CHARINDEX('Meta',@db_c_name) > 0
    begin
    continue
    end
    set @sql = N'use '+ QUOTENAME(@db_c_name) +';
    
WITH FirstPosts as (
SELECT MIN(Id) AS MinId
FROM Posts
WHERE PostTypeId=1
GROUP BY OwnerUserId
), VeteranPosts as (
SELECT Id
FROM Posts
WHERE PostTypeId=1
AND Id not in (Select MinId from FirstPosts)
)


    
   insert into #all_users 
SELECT 
'''+ QUOTENAME(@db_c_name) +''',
  COUNT(*) AS [Total Veteran Questions], 
  SUM(CASE WHEN AnswerCount = 0 OR AnswerCount IS NULL THEN 1 ELSE 0 END) AS [Unanswered Veteran Posts],
  SUM(CASE WHEN AnswerCount = 0 OR AnswerCount IS NULL THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS [Unanswered Veteran Posts Percentage],
  SUM(CASE WHEN CommentCount = 0 OR CommentCount IS NULL THEN 1 ELSE 0 END) AS [Uncommented Veteran Posts],
  SUM(CASE WHEN CommentCount = 0 OR CommentCount IS NULL THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS [Uncommented Veteran Posts Percentage],
  SUM(CASE WHEN (CommentCount = 0 OR CommentCount IS NULL) AND (AnswerCount = 0 OR AnswerCount IS NULL)  THEN 1 ELSE 0 END) AS [Unengaged Veteran Posts],
  SUM(CASE WHEN (CommentCount = 0 OR CommentCount IS NULL) AND (AnswerCount = 0 OR AnswerCount IS NULL)  THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS [Unengaged Veteran Posts Percentage]
FROM Posts p
WHERE Id in (Select * from VeteranPosts)
'
        
        
    exec (@sql)
end;
close db_c;
deallocate db_c;

select * from #all_users-- all databases

fork query

edited sep 20 19

created sep 20 19 jon.doe32184