共計 5184 個字符，預計需要花費 13 分鐘才能閱讀完成。
這篇文章主要介紹“sybase 數據庫怎么找出表大小腳本”，在日常操作中，相信很多人在 sybase 數據庫怎么找出表大小腳本問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”sybase 數據庫怎么找出表大小腳本”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學習吧！
以下 SQL 腳本用于找出表使用的空間大小，結果如圖
腳本如下：
/*
** 使用方法：isql -U -P -w 10000 -x 30 -s | -SMBFE2 -i1.sql -o1.out
** 使用說明：此腳本僅在 sybase15.5 版本上做過測試，因環境不同，可能不適用
** 結果說明：其實就是 sp_spaceused 存儲過程的結果放在一個輸出，單位為 MB
use dbname
set nocount on /* 禁用行的顯示 */
/* 定義 tab_name 游標為當前用戶 用戶表表名結果集 */
declare tab_name cursor
for select name
from sysobjects
where type= U
/* 打開游標 */
open tab_name
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 */
/* 關閉游標 */
close tab_name
/* 釋放游標 */
deallocate tab_name
go到此，關于“sybase 數據庫怎么找出表大小腳本”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注丸趣 TV 網站，丸趣 TV 小編會繼續努力為大家帶來更多實用的文章！
正文完
