Untagged questions (without locks), networkwide

0

Please login or register to vote for this query.

(click on this box to dismiss)

Lifehacks

Q&A for people looking to bypass life's everyday problems with simple tricks

create table #results (site nvarchar(250), tagcount int);

declare @sql nvarchar(max) = ''

select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , ''
) +
N'
select ''' + name + ''', count(*)
  from ' + quotename(name) + '.dbo.tags as t
  inner join ' + quotename(name) + '.dbo.posttags as pt on pt.tagid = t.id
  inner join ' + quotename(name) + '.dbo.posts as p on p.id = pt.postid
  left outer join ' + quotename(name) + '.dbo.suggestededits as se on p.id = se.postid
    and se.approvaldate is null and se.rejectiondate is null
  outer apply (
    select top 1 posthistorytypeid from ' + quotename(name) + '.dbo.posthistory
      where postid = p.id and posthistorytypeid in (14, 15)
      order by creationdate desc
      ) oa(posthistorytypeid)
  where t.tagname = ''untagged''
    and se.id is null
    and (oa.posthistorytypeid is null or oa.posthistorytypeid = 15)
'
from sys.databases
where database_id > 5

exec (@sql);

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results