Q&A for professional and amateur woodworkers
-- 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 declare @tag nvarchar(25) = ##tag:string## -- tag1 declare @tag2 nvarchar(25) = ##tag2:string## -- tag2 -- all databases declare db_c cursor for select [name] from sys.databases where database_id > 5 -- skip master, temp, model, msdb, Data.SE 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_tags ( site nvarchar(250) , creationdate datetime , tagcount int , tagname nvarchar(200)); 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_tags select @url , cast(p.creationdate as date) , count(*) as tagcount , tagname from posts p inner join posttags pt on pt.postid = p.id inner join tags t on t.id = pt.tagid where t.tagname in ('''+ @tag + ''','''+ @tag2 + ''') and year(CreationDate) = 2018 group by cast(p.creationdate as date) , t.tagname ;' exec (@sql) fetch next from db_c into @db_c_name end; close db_c; deallocate db_c; select /* replace( replace( replace(site, '.StackExchange.com',''), '.com',''), '.net','') as [site] ,*/ dateadd(d, -datepart(dw, creationdate), creationdate) , tagname , sum(tagcount) from #all_tags group by dateadd(d, -datepart(dw, creationdate), creationdate) , tagname order by dateadd(d, -datepart(dw, creationdate), creationdate) , tagname drop table #all_tags drop procedure #recursesiteurl drop procedure #siteurl