3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation

declare @tagname nvarchar(35) = 'sshfs'

-- result table
create table #all_tags ( site nvarchar(250)
                            , tagname nvarchar(35)
                            , tagcount int);

declare @sql nvarchar(max) = 'insert into #all_tags ' -- holds build up sql string

select @sql = @sql +
N'
select ''http://'' 
 + case PARSENAME('''+ name+''',1)
   when ''audio'' then ''avp''
   else PARSENAME(''' + name+''',1)
   end 
 + coalesce(''.'' 
          + case PARSENAME('''+ name +''',2)
            when ''audio'' then ''avp''
            else PARSENAME('''+name+''',2)
            end , '''')
 + IIF(patindex(''%.MathOverflow%'','''+ name+''') > 0
   , ''''
   , coalesce(''.'' + PARSENAME('''+ name+''',3), '''')
   )
 + IIF(patindex(''%.MathOverflow%'','''+name+''') > 0
   , ''.net''
   , ''.com''
   )
     , t.tagname
     , t.count
from ' + quotename(name) + '.dbo.tags t 
where t.tagname = ''' + @tagname + ''' collate sql_latin1_general_cp1_ci_ai
union '
from sys.databases
where database_id >5

set @sql = left(@sql, len(@sql)-6) 
print @sql
--exec (@sql)

select *
from #all_tags

view current version

edited jul 6 16

created jul 6 16 rene