sybase数据库怎么找出表大小脚本

这篇文章主要介绍"sybase数据库怎么找出表大小脚本"，在日常操作中，相信很多人在sybase数据库怎么找出表大小脚本问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答"sybase数据库怎么找出表大小脚本"的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

以下SQL脚本用于找出表使用的空间大小，结果如图

脚本如下：

/*** 使用方法：isql -U  -P   -w 10000 -x 30 -s '|' -SMBFE2 -i1.sql -o1.out** 使用说明：此脚本仅在sybase15.5版本上做过测试，因环境不同，可能不适用** 结果说明：其实就是sp_spaceused存储过程的结果放在一个输出，单位为MB*/use dbnamegoset nocount on  /*禁用行的显示*/go/* 定义tab_name游标为当前用户 用户表表名结果集 */declare tab_name cursorfor select name from sysobjects where type="U"go/* 打开游标 */open tab_namegobegin                declare @objname sysname    /* table name */        declare @empty_dpgs  int         /*                                        ** #empty data pages in hash region                                        ** of Virtually hashed table                                        */                /* 创建临时表:存放格式化后的结果 */        create table #fmtpgcounts (                name    char(35)                ,rowtotal       int                ,reserved       char(15)                ,data   char(15)                ,index_size     char(15)                ,unused char(15)        )        fetch next from tab_name into @objname            /* 读取游标的当前值，并把赋值给变量@tabname */        /* 循环条件：游标从结果集中读取完成时退出循环 */        while @@fetch_status = 0    begin                --print @objname                --exec sp_spaceused @objname                                /*                ** Obtain the page count for the target object in the current                ** database and store them in the temp table #pagecounts.                **                ** Note that we first retrieve the needed information from                ** sysindexes and we only then apply the OAM builtin system                ** functions on that data.  The reason being we want to relax                ** keeping the sh_int table lock on sysindexes for the duration                ** of the command.                */                select name = o.name,                        tabid = i.id,                        iname = i.name,                         indid = i.indid,                        low = d.low,                        rowtotal = convert(numeric(10,0), 0),                        reserved = convert(numeric(20, 9), 0),                        data = convert(numeric(20, 9), 0),                        index_size = convert(numeric(20, 9), 0),                        unused = convert(numeric(20, 9), 0)                into #pagecounts                 from sysobjects o, sysindexes i, master.dbo.spt_values d                                where i.id = object_id(@objname)                                        /*         --and i.indid = 0                                     0 = 表。             1 = 所有页锁定表上的聚簇索引。             >1 = DOL锁定表上的非聚簇索引或聚簇索引。             255 = text、 image、文本链或 Java 行外结构（大对象，即LOB 结构）。           */                                        and o.id = i.id                                        and d.number = 1                                        and d.type = "E"                                /* perform the row counts */                update #pagecounts                        set rowtotal = row_count(db_id(), tabid)                where indid <= 1                                /* calculate the counts for indid > 1                ** case of indid = 1, 0 are special cases done later                */                update #pagecounts set                        reserved = convert(numeric(20, 9),                            reserved_pages(db_id(), tabid, indid)),                        index_size =  convert(numeric(20, 9),                            data_pages(db_id(), tabid, indid)),                        unused = convert(numeric(20, 9),                                 ((reserved_pages(db_id(), tabid, indid) -                                  (data_pages(db_id(), tabid, indid)))))                where indid > 1                                /* calculate for case where indid = 0 */                update #pagecounts set        reserved = convert(numeric(20, 9),            reserved_pages(db_id(), tabid, indid)),        data = convert(numeric(20, 9),            data_pages(db_id(), tabid, indid)),        unused = convert(numeric(20, 9),                 ((reserved_pages(db_id(), tabid, indid) -                  (data_pages(db_id(), tabid, indid)))))    where indid = 0                /* handle the case where indid = 1, since we need                ** to take care of the data and index pages.                 */                update #pagecounts set                        reserved = convert(numeric(20, 9),                                     reserved_pages(db_id(), tabid, 0))                                   +  convert(numeric(20, 9),                                     reserved_pages(db_id(), tabid, indid)),                        index_size = convert(numeric(20, 9),                                     data_pages(db_id(), tabid, indid)),                        data = convert(numeric(20, 9),                                       data_pages(db_id(), tabid, 0))                where indid = 1                /* calculate the unused count for indid = 1 case.*/                update #pagecounts set                        unused = convert(numeric(20, 9),                                      reserved - data - index_size)                where indid = 1            /*            ** Check whether the table is Virtually hashed. For Virtually            ** Hashed tables, we maintain the number of empty pages in                ** systabstats. Compute the #data pages and #unused pages            ** based on that value.            */            if(exists(select convert(char(30),a.char_value)                    from sysattributes t, master.dbo.sysattributes c,                            master.dbo.sysattributes a                    where t.object_type = "T"                            and t.object = object_id(@objname)                            and c.class = 0 and c.attribute = 0                            and a.class = 0 and a.attribute = 1                            and t.class = c.object                            and t.class = a.object                            and t.attribute = a.object_info1                            and a.char_value = 'hash key factors'))                begin                                    select @empty_dpgs = emptypgcnt                                            from systabstats where id = object_id(@objname)                end      else          begin                              select @empty_dpgs = 0          end            insert into #fmtpgcounts            select distinct name,                    rowtotal = convert(int, sum(rowtotal)),                    reserved = convert(char(15), convert(varchar(11),                               convert(numeric(11, 0), sum(reserved) *                                     (low / 1024) / 1024)) + " " + "MB"),                    data = convert(char(15), convert(varchar(11),                           convert(numeric(11, 0), (sum(data) - @empty_dpgs) *                            (low / 1024)  / 1024)) + " " + "MB"),                    index_size = convert(char(15), convert(varchar(11),                                 convert(numeric(11, 0), sum(index_size) *                                 (low / 1024) / 1024)) + " " + "MB"),                    unused = convert(char(15), convert(varchar(11),                                 convert(numeric(11, 0), (sum(unused) + @empty_dpgs) *                                 (low / 1024) / 1024)) + " " + "MB")                from #pagecounts            drop table #pagecounts     /* 删除临时表 #pagecounts */                    fetch next from tab_name into @objname    end  select distinct    'TableName' = convert(char(35),name) ,          'RowTotal' = rowtotal ,                'Reserved' = convert(char(10), reserved),                'Data' = convert(char(10), data),                'IndexSize' = convert(char(10), index_size),                'Unused' = convert(char(10), unused)                         from #fmtpgcounts                             -- 去掉行数为0的行                             where rowtotal <> 0                             order by rowtotal desc  --exec sp_autoformat #fmtpgcounts        drop table #fmtpgcounts   /* 删除临时表 #fmtpgcounts */endgo/* 关闭游标 */close tab_namego/* 释放游标 */deallocate tab_namego

到此，关于"sybase数据库怎么找出表大小脚本"的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！