create table #results ( site nvarchar(250) , qt int , q int

0

Please login or register to vote for this query.

(click on this box to dismiss)

Meta Stack Exchange

Q&A for meta-discussion of the Stack Exchange family of Q&A websites

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , qt int
                       , q int
                       , qct int
                       , dq int
                       , cq int
                       , dcq int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , count(*) qt
     , sum(case when deletiondate is null then 1 else 0 end) q
     , sum(case when closeddate is not null then 1 else 0 end) qct
     , sum(case when deletiondate is not null then 1 else 0 end) dq
     , sum(case when deletiondate is null and closeddate is not null then 1 else 0 end) cq
     , sum(case when deletiondate is not null and closeddate is not null then 1 else 0 end) dcq
from ' + quotename(name) + '.dbo.postswithdeleted
where posttypeid =1
'
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

--print @sql

-- execute it
exec (@sql)

-- show results
select site
      -- rest of columns
      , qt as [al questions]
      , q as [visible questions]
      , qct as [all closed questons]
      , dq as [deleted questions]
      , cast(qct as decimal) / qt as [c/q ratio]
      , cq as [closed questions]
      , dcq as [delete closed questions]
from #results
order by cast(qct as decimal) / qt desc

drop table #results

Enter Parameters

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