dump tran sybsystemprocs with truncate_only
go

use sybsystemprocs
go

if exists (select * from sysobjects where name = "sp_showfrag" and type = 'P')
   drop proc sp_showfrag
go

create procedure sp_showfrag
@objname varchar(92) = null             /* the object we want size on */
as

/* Sccsid = "@(#) generic/sproc/spaceused 105.1 2/3/94" */
/*      4.8     1.1     06/14/90        sproc/src/spaceused */
 
/*
** Messages for "sp_showfrag"          17830
**
** 17460, "Object must be in the current database." 
** 17461, "Object does not exist in this database."
** 17830, "Object is stored in 'sysprocedures' and
**         has no space allocated directly."
** 17831, "Views don't have space allocated."
** 17832, "Not avail."
*/

/*
** IMPORTANT NOTE:
** This stored procedure uses the built-in function object_id() in the
** where clause of a select query. If you intend to change this query
** or use the object_id() or db_id() builtin in this procedure, please read the
** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
** pertaining to object-id's and db-id's outlined there, are followed.
 *
 * Name:   sp_showfrag
 * Date:   Feb 14, 1995
 * Author: Teresa Larson, Hughes STX (Teresa.A.Larson@gsfc.nasa.gov)
 *
 *
 * There is no warranty expressed or implied and the use of this code
 * is at your own risk.
 *
 * This procedure was created by modifying sp_spaceused so that a
 * listing for all tables is generated rather than showing information
 * one table at a time.
 *
*/


declare @type   smallint                /* the object type */
declare @msg    varchar(250)            /* message output */
declare @dbname varchar(30)             /* database name */
declare @tabname varchar(30)            /* table name */
declare @length int


if @@trancount = 0
begin
        set transaction isolation level 1
        set chained off
end

/*
**  Check to see that the objname is local.
*/
if @objname is not null
begin
        /*
        ** Get the dbname and ensure that the object is in the
        ** current database. Also get the table name - this is later
        ** needed to see if information is being requested for syslogs.
        */
        execute sp_namecrack @objname,
                             @db = @dbname output,
                             @object = @tabname output
        if @dbname is not NULL
        begin
                /*
                ** 17460, "Object must be in the current database." 
                */
                if (@dbname != db_name())
                begin
                        exec sp_getmessage 17460, @msg output
                        print @msg
                        return (1)
                end
        end

        /*
        **  Does the object exist?
        */
        if not exists (select *
                        from sysobjects
                                where id = object_id(@objname))
        begin
                /*
                ** 17461, "Object does not exist in this database."
                */
                exec sp_getmessage 17461, @msg output
                print @msg
                return (1)
        end

        /* Get the object type */
        select @type = sysstat & 7
                from sysobjects
                        where id = object_id(@objname)
        /*
        **  See if it's a space object.
        **  types are:
        **      0 - trigger
        **      1 - system table
        **      2 - view
        **      3 - user table
        **      4 - sproc
        **      6 - default
        **      7 - rule
        */
        if not exists (select *
                        from sysindexes
                                where id = object_id(@objname)
                                        and indid < 2)
        begin
                if @type in (0, 4, 6, 7)
                begin
                        /*
                        ** 17830, "Object is stored in 'sysprocedures' and
                        **         has no space allocated directly."
                        */
                        exec sp_getmessage 17830, @msg output
                        print @msg
                        return (1)
                end

                if @type = 2
                begin
                        /*
                        ** 17831, "Views don't have space allocated."
                        */
                        exec sp_getmessage 17831, @msg output
                        print @msg
                        return (1)
                end
        end

end

/*
**  First we want summary data.
*/
set nocount on

declare @slog_res_pgs numeric(20,9),    /* number of reserved pgs. in syslogs */
        @slog_dpgs numeric(20,9),       /* number of data pages in syslogs */
        @slog_unused numeric(20,9)      /* number of unused pages in syslogs */

/* Show the database name and size */
        
select distinct database_name = db_name(), database_size =
        ltrim(str(sum(size) / (1048576 / d.low), 10 ,1)) + " MB"
        from master.dbo.sysusages, master.dbo.spt_values d
                where dbid = db_id()
                        and d.number = 1
                        and d.type = "E"
                having dbid = db_id()
                        and d.number = 1
                        and d.type = "E"

/*
** Obtain the page count for syslogs table. 
*/
select @slog_res_pgs = convert(numeric(20,9),reserved_pgs(id, doampg)),
       @slog_dpgs = convert(numeric(20,9),data_pgs(id, doampg)),
       @slog_unused = convert(numeric(20,9),((reserved_pgs(id, doampg)+
                   reserved_pgs(id, ioampg)) - 
                   (data_pgs(id, doampg) + data_pgs(id, ioampg))))
from sysindexes where id = 8

/*
** Obtain the page count for all the objects in the current
** database; except for 'syslogs' (id = 8). Store the results
** in a temp. table (#pgcounts).
*/
select distinct
        sysindexes.name,
        res_pgs = (reserved_pgs(id, doampg) + reserved_pgs(id,ioampg)),
        low = d.low,
        dpgs = convert(numeric(20,9),data_pgs(id, doampg)),
        ipgs = convert(numeric(20,9),data_pgs(id, ioampg)), 
        unused = convert(numeric(20,9),((reserved_pgs(id, doampg) +
                   reserved_pgs(id, ioampg)) - 
                   (data_pgs(id, doampg) + data_pgs(id, ioampg))))
into #pgcounts 
from sysindexes, master.dbo.spt_values d
        where sysindexes.id != 8
                and d.number = 1 
                and d.type = "E" 
        having d.number = 1
                and d.type = "E"
        
/*
** Compute the summary results by adding page counts from
** individual data objects. Add to the count the count of 
** pages for 'syslogs'.  Convert the total pages to space
** used in Kilo bytes.
*/
select distinct reserved = convert(char(15), convert(varchar(11),
        convert(numeric(11,0),((sum(res_pgs) + @slog_res_pgs) /
                1024) * low)) + " " + "KB"),
        data = convert(char(15), convert(varchar(11),
                convert(numeric(11,0),((sum(dpgs) + @slog_dpgs) / 
                1024) * low)) + " " + "KB"),
        index_size = convert(char(15), convert(varchar(11),
                convert(numeric(11,0), (sum(ipgs) / 1024) * low))
                + " " + "KB"),
        unused = convert(char(15), convert(varchar(11),
                convert(numeric(11,0),((sum(unused) + @slog_unused) / 
                1024) * low)) + " " + "KB")
from #pgcounts

/* collect the object information into a temp table */

select name, id, type
into #objlist
from sysobjects where type in ('S', 'U')

/*
**  Now we want detail on all objects
*/

        if (@tabname = "syslogs") /* syslogs */
        begin
                /*
                ** 17832, "Not avail."
                */
                exec sp_getmessage 17832, @msg output

                select @length = max(datalength(o.name))
                    from sysobjects o, sysindexes i, #objlist
                        where i.id = #objlist.id
                                and o.id = #objlist.id

                if (@length > 20)
                    select name = o.name,
                        rowtotal = convert(char(11), @msg),
                        reserved = convert(char(10), convert(varchar(11),
                                convert(numeric(11,0), convert(numeric(20,9),
                                (reserved_pgs(i.id, i.doampg) / 1024)) * 
                                d.low)) + " " + "KB"),
                        data = convert(char(10), convert(varchar(11),
                                convert(numeric(11,0),convert(numeric(20,9),
                                data_pgs(i.id, i.doampg) / 1024) * d.low)) + 
                                " " + "KB"),
                        index_size = convert(char(10), convert(varchar(11), 0) 
                                + " " + "KB"),
                        unused = convert(char(10), convert(varchar(11),
                                convert(numeric(11,0), 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)))) / 1024 * d.low)) + " " + "KB")
                        from sysobjects o, sysindexes i,
                             master.dbo.spt_values d, #objlist
                                where i.id = #objlist.id
                                        and o.id = #objlist.id
                                        and d.number = 1
                                        and d.type = "E"
                else
                    select name = convert(char(20), o.name),
                        rowtotal = convert(char(11), @msg),
                        reserved = convert(char(10), convert(varchar(11),
                                convert(numeric(11,0), convert(numeric(20,9),
                                (reserved_pgs(i.id, i.doampg) / 1024)) * 
                                d.low)) + " " + "KB"),
                        data = convert(char(10), convert(varchar(11),
                                convert(numeric(11,0),convert(numeric(20,9),
                                data_pgs(i.id, i.doampg) / 1024) * d.low)) + 
                                " " + "KB"),
                        index_size = convert(char(10), convert(varchar(11), 0) 
                                + " " + "KB"),
                        unused = convert(char(10), convert(varchar(11),
                                convert(numeric(11,0), 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)))) / 1024 * d.low)) + " " + "KB")
                        from sysobjects o, sysindexes i,
                             master.dbo.spt_values d, #objlist
                                where i.id = #objlist.id
                                        and o.id = #objlist.id
                                        and d.number = 1
                                        and d.type = "E"
        end
        else
        begin

                select name = o.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))))
                into #pagecounts
                from sysobjects o, sysindexes i, master.dbo.spt_values d,
                        #objlist
                                where i.id = #objlist.id
                                        and o.id = #objlist.id
                                        and d.number = 1
                                        and d.type = "E"

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

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

return (0)
go

if object_id('sp_showfrag') is not null
begin
    print '<<< Created procedure dbo.sp_showfrag >>>'
    grant execute on dbo.sp_showfrag to public
end
else
begin
    print '<<< Failed creating proc dbo.sp_showfrag >>>'
end
go