Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles
/* Inspiration for cross-site query from: Mark Hurd, http://data.stackexchange.com/stackoverflow/query/69700/generate-emailhash-comparison Tim Stone, http://data.stackexchange.com/stackoverflow/query/72411 */ /* 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. http://dba.stackexchange.com ~~ a literal '~' */ declare @firstToK as int = ##FirstTo:int?10000##; declare @sitedbname as NVarchar(100), @sitedispname as NVarchar(100), @query as nvarchar(MAX), @firstvote as DateTime; create table #sitebegin ( DName NVarchar(100), FirstUser DateTime ); create table #sitestats ( Id int identity not null, Site NVarchar(250), DName NVarchar(100), UserName NVarchar(50), UserLink NVarchar(250), DaysToRep int); 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 BEGIN declare @org as NVarchar(100), @http as NVarchar(100), @pos as int; select @org = lower(@sitedbname), @http = 'http://', @pos = CharIndex('.', @sitedbname), @sitedispname = case when @sitedbname like 'StackExchange.%' then substring(@sitedbname, 15, 100) else @sitedbname end; 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' set @query = ' insert into #sitebegin select ''' + @sitedispname + ''', min(CreationDate) from [' + @sitedbname + ']..Users '; EXEC sp_executesql @query; select @firstvote = FirstUser from #sitebegin where DName = @sitedispname print 'Processing site ' + @sitedbname + ' -> ' + @http + ' started ' + Convert(NVarchar(max), @firstvote); fetch next from SITES into @sitedbname END close SITES deallocate SITES ; select * from #sitebegin