建站
咨询
售后
建站咨询
sybase数据库怎么找出表大小脚本
这篇文章主要介绍“sybase数据库怎么找出表大小脚本”，在日常操作中，相信很多人在sybase数据库怎么找出表大小脚本问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”sybase数据库怎么找出表大小脚本”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：主机域名、网络空间、营销软件、网站建设、都昌网站维护、网站推广。
以下SQL脚本用于找出表使用的空间大小，结果如图
脚本如下：
/* ** 使用方法：isql -U -P -w 10000 -x 30 -s '|' -SMBFE2 -i1.sql -o1.out ** 使用说明：此脚本仅在sybase15.5版本上做过测试，因环境不同，可能不适用 ** 结果说明：其实就是sp_spaceused存储过程的结果放在一个输出，单位为MB */ use dbname go set nocount on /*禁用行的显示*/ go /* 定义tab_name游标为当前用户 用户表表名结果集 */ declare tab_name cursor for select name from sysobjects where type="U" go /* 打开游标 */ open tab_name go begin 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 */ end go /* 关闭游标 */ close tab_name go /* 释放游标 */ deallocate tab_name go
到此，关于“sybase数据库怎么找出表大小脚本”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注创新互联网站，小编会继续努力为大家带来更多实用的文章！
网站栏目：sybase数据库怎么找出表大小脚本
分享URL：http://scyingshan.cn/article/jedgji.html
