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

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

脚本如下：

/***使用方法：isql-U-P-w10000-x30-s'|'-SMBFE2-i1.sql-o1.out**使用说明：此脚本仅在sybase15.5版本上做过测试，因环境不同，可能不适用**结果说明：其实就是sp_spaceused存储过程的结果放在一个输出，单位为MB*/usedbnamegosetnocounton/*禁用行的显示*/go/*定义tab_name游标为当前用户用户表表名结果集*/declaretab_namecursorforselectnamefromsysobjectswheretype="U"go/*打开游标*/opentab_namegobegindeclare@objnamesysname/*tablename*/declare@empty_dpgsint/***#emptydatapagesinhashregion**ofVirtuallyhashedtable*//*创建临时表:存放格式化后的结果*/createtable#fmtpgcounts(namechar(35),rowtotalint,reservedchar(15),datachar(15),index_sizechar(15),unusedchar(15))fetchnextfromtab_nameinto@objname/*读取游标的当前值，并把赋值给变量@tabname*//*循环条件：游标从结果集中读取完成时退出循环*/while@@fetch_status=0begin--print@objname--execsp_spaceused@objname/***Obtainthepagecountforthetargetobjectinthecurrent**databaseandstoretheminthetemptable#pagecounts.****Notethatwefirstretrievetheneededinformationfrom**sysindexesandweonlythenapplytheOAMbuiltinsystem**functionsonthatdata.Thereasonbeingwewanttorelax**keepingthesh_inttablelockonsysindexesfortheduration**ofthecommand.*/selectname=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#pagecountsfromsysobjectso,sysindexesi,master.dbo.spt_valuesdwherei.id=object_id(@objname)/*--andi.indid=00=表。1=所有页锁定表上的聚簇索引。>1=DOL锁定表上的非聚簇索引或聚簇索引。255=text、image、文本链或Java行外结构（大对象，即LOB结构）。*/ando.id=i.idandd.number=1andd.type="E"/*performtherowcounts*/update#pagecountssetrowtotal=row_count(db_id(),tabid)whereindid<=1/*calculatethecountsforindid>1**caseofindid=1,0arespecialcasesdonelater*/update#pagecountssetreserved=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)))))whereindid>1/*calculateforcasewhereindid=0*/update#pagecountssetreserved=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)))))whereindid=0/*handlethecasewhereindid=1,sinceweneed**totakecareofthedataandindexpages.*/update#pagecountssetreserved=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))whereindid=1/*calculatetheunusedcountforindid=1case.*/update#pagecountssetunused=convert(numeric(20,9),reserved-data-index_size)whereindid=1/***CheckwhetherthetableisVirtuallyhashed.ForVirtually**Hashedtables,wemaintainthenumberofemptypagesin**systabstats.Computethe#datapagesand#unusedpages**basedonthatvalue.*/if(exists(selectconvert(char(30),a.char_value)fromsysattributest,master.dbo.sysattributesc,master.dbo.sysattributesawheret.object_type="T"andt.object=object_id(@objname)andc.class=0andc.attribute=0anda.class=0anda.attribute=1andt.class=c.objectandt.class=a.objectandt.attribute=a.object_info1anda.char_value='hashkeyfactors'))beginselect@empty_dpgs=emptypgcntfromsystabstatswhereid=object_id(@objname)endelsebeginselect@empty_dpgs=0endinsertinto#fmtpgcountsselectdistinctname,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#pagecountsdroptable#pagecounts/*删除临时表#pagecounts*/fetchnextfromtab_nameinto@objnameendselectdistinct'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的行whererowtotal<>0orderbyrowtotaldesc--execsp_autoformat#fmtpgcountsdroptable#fmtpgcounts/*删除临时表#fmtpgcounts*/endgo/*关闭游标*/closetab_namego/*释放游标*/deallocatetab_namego

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