Sp tempdbuse--Check usage of all tempdbs

This sp cycles through all databases with 'tempdb' in the name and outputs usage information - size, percent used etc.

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