Top 50 question bodies of all SE sites


Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

  Inspiration for cross-site query from:
    Mark Hurd,
    Tim Stone,
  We build a dynamic query as a prelude, then @Template for each site
  with @Separator in between, and finally a postlude.
  First, the template, which may contain the following escape strings:
    ~n  the "pretty" site name (or close enough), e.g. 'Dba'
    ~u  the URL of the site, e.g.
    ~~  a literal '~'
create table #bodies (
    Body NVarchar(max));
declare @sitedbname as NVarchar(100),
        @query as NVarchar(1000),
        @Body as NVarchar(max)

select TOP 250 @Body = Body
from Posts

declare SITES cursor for

select Substring(Name, 0, Len(Name) - 4 )
from sys.Databases
where Name like '%.Meta'
  --and Name = 'StackExchange.Codereview.Meta'
  and not Name = 'StackExchange.Meta'
UNION select 'StackExchange.Meta'

open SITES

fetch next from SITES into @sitedbname
while @@FETCH_STATUS = 0

    declare @org as NVarchar(100), @http as NVarchar(100), @pos as int;
    select @org = lower(@sitedbname),
           @http = 'http://',
           @pos = CharIndex('.', @sitedbname);
    if @pos > 0 begin
        select @http = @http + substring(@org, @pos + 1, len(@org) - @pos) + '.',
               @org = left(@org, @pos - 1)
    select @http = @http + @org + '.com/users/'

    print 'Processing site ' + @sitedbname + ' -> ' + @http
    set @query = '
           insert into #bodies
           select TOP 250 Body
           FROM [' + @sitedbname + ']..Posts
           WHERE PostTypeId = 1
           ORDER BY Score DESC'
    print @query

    EXEC sp_executesql @query

    fetch next from SITES into @sitedbname

close SITES
deallocate SITES

select Body
from #bodies

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results