Site graduation dates


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,
#table style tables are available to the dynamic SQL.
the best bet is to build a cursor of all databases in the system that matter.
The easist way to do that is to look for all databases that come in pairs,
a regular database and the corresponding meta site database. E.g.

 - StackExchange.Codereview.Meta
 - StackExchange.Codereview

There are other databases in the system tables, but they do not have a matching
meta database.

Unfortunately, the site does not have a meta, so add it in.

declare SITES cursor for
    select Substring(Name, 0, Len(Name) - 4)
    from sys.Databases
    where Name like '%.Meta'
     and not Name = 'StackExchange.Meta'
    select 'StackExchange.Meta'

create table #sitestats (
    Site NVarchar(250),
    Age Date,
    Election Date);
declare @sitedbname as NVarchar(100),
        @query as NVarchar(1000)
open SITES

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

    declare @org as NVarchar(100), @http as NVarchar(100), @pos as int;

    -- convert the DBName in to a URL for the result set.
    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'

    print 'Processing site ' + @sitedbname + ' -> ' + @http
    --Add whatever sub-URL you want to the http url:
    select @http = @http + '/users/'
    -- build and run your dynamic query. Note how you prefix the tables you need
    set @query = '
           insert into #sitestats
           select ''' + @http + '|' + @sitedbname + ''',
           Convert(Date, Min("Date")),
           Convert(Date, Min(case when Name = ''Caucus'' then "Date" else NULL end))
           FROM [' + @sitedbname + ']..Badges'
    EXEC sp_executesql @query

    fetch next from SITES into @sitedbname

close SITES
deallocate SITES

select Site,
       -- SubString(Site, 1, CharIndex('|', Site) - 1) + Convert(NVarchar, Id) + Substring(Site, CharIndex('|', Site), 100) as Site,
from #sitestats
order by Election desc, Age desc

Enter Parameters

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