sybase 计算表大小的脚本

/*
** 使用方法：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