叫你如何在sybase数据库找出表大小脚本

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

脚本如下：

/*
**使用方法：isql-U-P-w10000-x30-s'|'-SMBFE2-i1.sql-o1.out
**使用说明：此脚本仅在sybase15.5版本上做过测试，因环境不同，可能不适用
**结果说明：其实就是sp_spaceused存储过程的结果放在一个输出，单位为MB
*/

usedbname
go

setnocounton/*禁用行的显示*/
go

/*定义tab_name游标为当前用户用户表表名结果集*/
declaretab_namecursor
forselectname
fromsysobjects
wheretype="U"
go
/*打开游标*/
opentab_name
go

begin
	
	declare@objnamesysname/*tablename*/
	declare@empty_dpgsint		/*
					**#emptydatapagesinhashregion
					**ofVirtuallyhashedtable
					*/
	
	/*创建临时表:存放格式化后的结果*/
	createtable#fmtpgcounts(
		name	char(35)
		,rowtotal	int
		,reserved	char(15)
		,data	char(15)
		,index_size	char(15)
		,unused	char(15)
	)


	fetchnextfromtab_nameinto@objname		/*读取游标的当前值，并把赋值给变量@tabname*/
	/*循环条件：游标从结果集中读取完成时退出循环*/
	while@@fetch_status=0
begin
		--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#pagecounts
		fromsysobjectso,sysindexesi,master.dbo.spt_valuesd
				wherei.id=object_id(@objname)
					/*	--andi.indid=0
				0=表。
1=所有页锁定表上的聚簇索引。
>1=DOL锁定表上的非聚簇索引或聚簇索引。
255=text、image、文本链或Java行外结构（大对象，即LOB结构）。
*/
					ando.id=i.id
					andd.number=1
					andd.type="E"
		
		/*performtherowcounts*/
		update#pagecounts
			setrowtotal=row_count(db_id(),tabid)
		whereindid<=1
		
		/*calculatethecountsforindid>1
		**caseofindid=1,0arespecialcasesdonelater
		*/
		update#pagecountsset
			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)))))
		whereindid>1
		
		/*calculateforcasewhereindid=0*/
		update#pagecountsset
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)))))
whereindid=0


		/*handlethecasewhereindid=1,sinceweneed
		**totakecareofthedataandindexpages.
		*/
		update#pagecountsset
			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))
		whereindid=1

		/*calculatetheunusedcountforindid=1case.*/
		update#pagecountsset
			unused=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.sysattributesa
		wheret.object_type="T"
			andt.object=object_id(@objname)
			andc.class=0andc.attribute=0
			anda.class=0anda.attribute=1
			andt.class=c.object
			andt.class=a.object
			andt.attribute=a.object_info1
			anda.char_value='hashkeyfactors'))
	begin
				select@empty_dpgs=emptypgcnt
					fromsystabstatswhereid=object_id(@objname)
	end
else
	begin
				select@empty_dpgs=0
	end

	insertinto#fmtpgcounts
	selectdistinctname,
		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

	droptable#pagecounts/*删除临时表#pagecounts*/

		fetchnextfromtab_nameinto@objname
end

selectdistinct
'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<>0
			orderbyrowtotaldesc

--execsp_autoformat#fmtpgcounts
	droptable#fmtpgcounts/*删除临时表#fmtpgcounts*/
end

go
/*关闭游标*/
closetab_name
go
/*释放游标*/
deallocatetab_name
go