IF OBJECT_ID ( '#siteurl', 'P' ) IS NOT NULL DROP PROCEDU...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- 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
      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 #siteurl @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

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_badges ( site nvarchar(250)
                         , name nvarchar(40)
                         , [class] int
                         , cnt int);
                            
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_badges 
               select @url
                   , b.name
                   , b.class
                   , count(*)
                   -- , b.userid
              from badges b
              -- inner join users u on u.id = b.userid
              where -- u.accountid = + cast(@accountid as nvarchar) + 
              tagbased = 0
              group by b.name, b.class
              ;'
    exec (@sql)
   fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;


select name as [badge name]
     , case 
       when class = 1 then 'Gold'
       when class = 2 then 'Slver'
       when class = 3 then 'Bronze'
       end as Class
       , count(*) as [On sites]
       , sum(cnt) as [awarded]
from #all_badges
group by name , class
order by count(*) desc

drop table #all_badges

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results