use master
go
alter procedure sp_checktabletempsize
  @objid int,
  @f_secindexes bit,
  @total decimal(38) = 0 OUTPUT
as
/*
 Use decimal(28) everywhere if -p38 option is not used for system.
 Hope MS don't mind me correcting this overflow error.
*/
  declare @IndexObjSize int,
          @ForwardObjSize int,
          @PageObjSize int,
          @TextObjSize int,
          @overhead int,
          @indextotal decimal(38),
          @pagetotal decimal(38),
          @texttotal decimal(38),
          @forwardtotal decimal(38),
          @query varchar(500)

  set @overhead = 20    
  set @IndexObjSize = 100 + @overhead
  set @ForwardObjSize = 52 + @overhead
  set @PageObjSize = 44 + @overhead
  set @TextObjSize = 72 + @overhead

  /* Index Objects */
  /* status & 32 detects hypothetical indexes,
     status & 8  detects dummy tables */
  select @indextotal = sum(((maxirow*2) + @IndexObjSize) * CAST(dpages as decimal(28)) * 3)
  from sysindexes
  where indid > 0 and
    indid < 255 and
    id = @objid and
    ((@f_secindexes = 1) or id = 1) and
    (status & 32 = 0) and
    (status & 8 = 0)
  if @indextotal is null
    set @indextotal = 0

  /* Page Objects */ 
  select @pagetotal = sum((used + (reserved % 8) +
                        (CAST(reserved as decimal(28))/ 8)) * @PageObjSize * 2)
  from sysindexes
  where id = @objid and
    used < 4096
  if @pagetotal is null
    set @pagetotal = 0
  /* Text Objects */
  select @texttotal = sum(totals.total)
  from
    (select ((si1.rows * count(sc.id)) + (si2.used * 2)) * @TextObjSize * 2 as total
     from sysindexes si1, sysindexes si2, syscolumns sc
     where si1.id = si2.id and
           si1.indid in (0,1) and
           si2.indid = 255 and
           sc.id = si1.id and
           sc.xtype in (34,99,35) and
           si1.id = @objid
     group by si1.id, si1.rows, si2.used
    ) totals
  if @texttotal is null
    set @texttotal = 0

  /* Forward Objects */
  select @forwardtotal = sum((rows/100) * @ForwardObjSize * 2)
  from sysindexes where indid = 0
    and id = @objid

  if @forwardtotal is null
    set @forwardtotal = 0

  /* The 1.1 is a "fudge factor" for extra page overhead */
  set @total = CAST(((@indextotal + @pagetotal + @texttotal +
                                        @forwardtotal) * 1.1) as decimal(38))
  /* the 1.4 is another "fudge factor" */
  set @total = CAST((@total * 1.4) as decimal(38)) / 1024

Go
exec sp_MS_marksystemobject 'sp_checktabletempsize'
go

This page was last updated on May 01, 2006 04:28 PM.