Sp tempdbuse--Check usage of all tempdbs

create procedure sp__tempdbuse as

---

-- Procedure: sp__tempdbuse -- Created: May 2008 -- Author: Bob Holmes (Email: cambob@gmail.com) -- Usage: sp__tempdbuse

---

set nocount on

select name into #dblist from master..sysdatabases where name like "%tempdb%"

declare db_cursor cursor for select name from #dblist

declare @current char(20)

open db_cursor

fetch db_cursor into @current

create table #results (dbname char(20), totalspace char(10), freespace char(10),

pctused char(3))

while @@sqlstatus = 0 begin

 select  db_name(dbid) 'DatabaseName', segmap 'Segmap', name 'Segname',
         sum(size) 'Allocated',
         sum(curunreservedpgs(dbid,lstart,unreservedpgs)) 'Free'
 into    #SegAlloc
 from    master.dbo.sysusages , syssegments
 where   dbid = db_id(@current) and
         segmap & power(2,segment) = power(2,segment)
         and syssegments.name != 'system'

and syssegments.name != 'logsegment'

 group by dbid,segmap,name
 order by dbid,segmap,name

 insert #results  
 select  DatabaseName, 
         rtrim(convert(char(10),sum(Allocated)/512)),
         rtrim(convert(char(10),sum(Free)/512)),
         rtrim(convert(char(10),100 -(sum((Free)/512) * 100 / (sum

(Allocated)/512))))

 from    #SegAlloc
 group by DatabaseName,Segname
 order by DatabaseName,Segname

 drop table #SegAlloc

 fetch db_cursor into @current

end

select * from #results

close db_cursor

deallocate cursor db_cursor

return