use sybsystemprocs
go

IF OBJECT_ID('dbo.sp_spaceused_table') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.sp_spaceused_table
    IF OBJECT_ID('dbo.sp_spaceused_table') IS NOT NULL
        PRINT '<<< FAILED TO DROP dbo.sp_spaceused_table >>>'
    ELSE
        PRINT '<<< DROPPED PROC dbo.sp_spaceused_table >>>'
END
go

create procedure sp_spaceused_table
@list_indices  int = 0
as
declare @type         smallint,      -- the object type
        @msg          varchar(250),  -- message output
        @dbname       varchar(30),   -- database name
        @tabname      varchar(30),   -- table name
        @length       int,
        @object_id    int

set nocount on

if @@trancount = 0
begin
    set chained off
end

set transaction isolation level 1

create table #pagecounts
   (
    name        varchar(45)   null,
    iname       varchar(45)   null,
    low         int           null,
    rowtotal    int           null,
    reserved    numeric(20,9) null,
    data        numeric(20,9) null,
    index_size  numeric(20,9) null,
    unused      numeric(20,9) null
   )

select @object_id = min(id)
  from sysobjects
 where type = 'U'
   and name not like "%pagecount%"

while (@object_id is not null)
begin
    /*
    **  We want a particular object.
    */
    insert #pagecounts
    select  name       = o.name,
            iname      = i.name,
            low        = d.low,
            rowtotal   = rowcnt(i.doampg),
            reserved   = convert(numeric(20,9),
                                 (reserved_pgs(i.id, i.doampg) +
                                  reserved_pgs(i.id, i.ioampg))),
            data       = convert(numeric(20,9), data_pgs(i.id, i.doampg)),
            index_size = convert(numeric(20,9), data_pgs(i.id, i.ioampg)),
            unused = convert(numeric(20,9),
                             ((reserved_pgs(i.id, i.doampg) +
                               reserved_pgs(i.id, i.ioampg)) -
                               (data_pgs(i.id, i.doampg) +
                                data_pgs(i.id, i.ioampg))))
      from sysobjects o
          ,sysindexes i
          ,master.dbo.spt_values d
     where i.id     = @object_id
       and o.id     = @object_id
       and i.id     = o.id
       and d.number = 1
       and d.type   = 'E'

    select @object_id = min(id)
      from sysobjects
     where type = 'U'
       and id   > @object_id
       and name not like "%pagecount%"

end

select @length = max(datalength(iname))
  from #pagecounts

if (@list_indices = 1)
begin

    if (@length > 20)
    begin
        select  index_name = iname,
                size = convert(char(10), convert(varchar(11),
                       convert(numeric(11,0),
                               index_size / 1024 *
                               low)) + ' KB'),
                reserved = convert(char(10),
                           convert(varchar(11),
                           convert(numeric(11,0),
                                   reserved / 1024 *
                                   low)) + ' KB'),
                unused = convert(char(10), convert(varchar(11),
                         convert(numeric(11,0), unused / 1024 *
                                        low)) + ' KB')
        from #pagecounts

    end
    else
    begin
        select  index_name = convert(char(20), iname),
                size = convert(char(10), convert(varchar(11),
                       convert(numeric(11,0),
                                        index_size / 1024 *
                                        low)) + ' KB'),
                reserved = convert(char(10),
                                   convert(varchar(11),
                                           convert(numeric(11,0),
                                                   reserved / 1024 *
                                                   low)) + ' KB'),
                unused = convert(char(10), convert(varchar(11),
                         convert(numeric(11,0), unused / 1024 *
                                        low)) + ' KB')
        from #pagecounts
    end
end

if (@length > 20)
begin
    select distinct name,
           rowtotal = convert(char(11), sum(rowtotal)),
           reserved = convert(char(15), convert(varchar(11),
                      convert(numeric(11,0), sum(reserved) *
                                             (low / 1024))) + ' KB'),
           data = convert(char(15), convert(varchar(11),
                  convert(numeric(11,0), sum(data) * (low / 1024)))
                   + ' KB'),
           index_size = convert(char(15), convert(varchar(11),
                    convert(numeric(11,0), sum(index_size) *
                    (low / 1024))) + ' KB'),
           unused = convert(char(15), convert(varchar(11),
                    convert(numeric(11,0), sum(unused) *
                    (low / 1024))) + ' KB')
      from #pagecounts
     group by name
end
else
begin
    select distinct name = convert(char(20), name),
           rowtotal = convert(char(11), sum(rowtotal)),
           reserved = convert(char(15), convert(varchar(11),
                   convert(numeric(11,0), sum(reserved) *
                   (low / 1024))) + ' KB'),
           data = convert(char(15), convert(varchar(11),
                   convert(numeric(11,0), sum(data) * (low / 1024)))
                   + ' KB'),
           index_size = convert(char(15), convert(varchar(11),
                    convert(numeric(11,0), sum(index_size) *
                    (low / 1024))) + ' KB'),
           unused = convert(char(15), convert(varchar(11),
                    convert(numeric(11,0), sum(unused) *
                    (low / 1024))) + ' KB')
      from #pagecounts
     group by name
end

return (0)
go

IF OBJECT_ID('dbo.sp_spaceused_table') IS NOT NULL
    PRINT '<<< CREATED PROC dbo.sp_spaceused_table >>>'
ELSE
    PRINT '<<< FAILED TO CREATE PROC dbo.sp_spaceused_table >>>'
go
