Q&A about the site for developers and users of the secure, private and untraceable cryptocurrency Monero
-- start create url from dbname IF OBJECT_ID ( '#siteurl', 'P' ) IS NOT NULL DROP PROCEDURE #siteurl; GO create procedure #siteurl @dbname nvarchar(250), @res nvarchar(250) OUT as begin -- we have three sites that have borked DNS entries set @dbname = (case @dbname when 'StackExchange.Audio' then 'StackExchange.Avp' when 'StackExchange.Audio.Meta' then 'StackExchange.Avp.Meta' when 'StackExchange.Mathoverflow.Meta' then 'net.Mathoverflow.meta' else @dbname end) -- and one of those doesn't want to end with .com if @dbname <> 'net.Mathoverflow.meta' set @dbname = 'com.' + @dbname exec #recursesiteurl @dbname, @res OUTPUT end GO -- create url from dbname IF OBJECT_ID ( '#recursesiteurl', 'P' ) IS NOT NULL DROP PROCEDURE #recursesiteurl; GO create procedure #recursesiteurl @dbname nvarchar(250), @res nvarchar(250) OUT as begin declare @pos integer declare @rec nvarchar(250) set @res = @dbname set @pos = CHARINDEX('.', @dbname) if (@pos > 0 ) begin set @rec = substring(@dbname, @pos+1, len(@dbname)) exec #recursesiteurl @rec, @rec output set @res = @rec + '.' + substring(@dbname, 0, @pos) end end; GO -- all databases declare db_c cursor for select [name] from sys.databases where database_id > 5 -- skip master, temp, model, msdb, Data.SE and ([name] not like '%.meta' or [Name] = 'Meta.StackExchange') declare @db_c_name sysname -- holds name of db after fetch declare @sql nvarchar(max) -- holds build up sql string -- result table create table #all_results (site nvarchar(250) , countPosts bigint); open db_c fetch next from db_c into @db_c_name while(@@FETCH_STATUS = 0) begin set @sql = N'use '+ QUOTENAME(@db_c_name) +'; declare @url nvarchar(250) exec #siteurl ''' + @db_c_name + ''', @url output insert into #all_results select @url , count(*) from PostsWithDeleted where CreationDate >= ''##start##'' and CreationDate < ''##end##'';' exec (@sql) fetch next from db_c into @db_c_name end; close db_c; deallocate db_c; select lower( replace( replace( replace(site, '.StackExchange.com',''), '.com',''), '.net','')) as [site] , countPosts from #all_results drop table #all_results drop procedure #recursesiteurl drop procedure #siteurl