0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

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

create table #sitestats (
    Site NVarchar(250),
    SName NVarchar(100),
    PostDate DateTime,
    PostCount integer);
    
declare @sitedbname as NVarchar(100),
        @sitemetaname as NVarchar(100),
        @query as NVarchar(1000),
        @metric as integer,
        @elections as NVarchar(10)
    ;
    
open SITES

fetch next from SITES into @sitedbname, @sitemetaname
while @@FETCH_STATUS = 0
BEGIN

    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)
    end
    select @http = @http + @org + '.com'

    --print 'Processing site ' + @sitedbname + ' -> ' + @http
    
    --Add whatever sub-URL you want to the http url:
    select @http = @http + '/users/'
    
    set @query = '
           insert into #sitestats
           select ''' + @http + '|' + @sitedbname + ''', ''' + @sitedbname + ''', cast (CreationDate as Date), Count(*)
           from [' + @sitedbname + ']..Posts
           group by cast (CreationDate as Date)'; 
           
    EXEC sp_executesql @query

    fetch next from SITES into @sitedbname, @sitemetaname
END

close SITES
deallocate SITES
;

select *
from #sitestats
order by SName, PostDate
;

fork query

created aug 23 16 rolfl