ASE15.0中利用代理表實現統計使用者表格儲存體空間大小的功能

----------------------------------------------------------------------------

---- 本文為andkylee個人原創，請在尊重作者勞動成果的前提下進行轉載；

---- 轉載務必註明原始出處

:

http://blog.csdn.net/andkylee

--- 2010-08-17 16:08:26

---- 關鍵字：ASE sp_spaceused 代理表 預存程序 統計表的空間大小 行數最多的表的記錄數

                 所佔空間最大的表

----------------------------------------------------------------------------

 

在ASE中統計某個使用者表的資訊，比如：表中的行數、分配的空間、資料所佔空間、索引所佔空間以及剩餘空間等，我們可以使用系統預存程序sp_spaceused來實現。

 

系統預存程序sp_spaceused有兩個參數：@objname和@list_indices，第一個@objname是待統計的對象名，一般是表名；第二個@list_indices標誌是否單獨統計索引資訊，@list_indices=1表示單獨統計索引資訊，@list_indices=0則不單獨統計。

 

樣本：

1> sp_spaceused sysobjects,0<br />2> go<br /> name rowtotal reserved data index_size unused<br /> ---------- -------- -------- ------ ---------- ------<br /> sysobjects 1014 252 KB 132 KB 68 KB 52 KB<br />(1 row affected)<br />(return status = 0)<br />1> sp_spaceused sysobjects,1<br />2> go<br /> index_name size reserved unused<br /> ------------ ----- -------- ------<br /> ncsysobjects 48 KB 64 KB 16 KB<br /> csysobjects 20 KB 32 KB 12 KB<br />(1 row affected)<br /> name rowtotal reserved data index_size unused<br /> ---------- -------- -------- ------ ---------- ------<br /> sysobjects 1014 252 KB 132 KB 68 KB 52 KB<br />(return status = 0)<br />1>

 

但是利用sp_spaceused還不能實現統計某個庫裡面哪張表的資料記錄數最多，哪張表佔用的空間最大，哪些表的行數為0，以及哪些表的索引所佔空間大於100M等等的問題。

 

如何?呢？

 

方法一：改造sp_spaceused過程文法中的SQL語句。但是，sp_spaceused過程的原始碼有560多行，看起來比較累，至少需要一個暫存資料表儲存中間臨時資料。

方法二：是本部落格專門推薦使用的，利用ASE中的代理表來實現。下面是操作步驟：

 

假設使用者資料庫名字為：andkylee

 

先執行：use andkylee

          go

 

步驟一：建立代理表tablespace2

 

create  existing table tablespace2(name varchar(128) null,rowtotal char(15) ,reserved char(15),[data] char(15),index_size char(15),unused char(15),_objname varchar(128) null,_list_indices int null) external procedure at "loopback
.andkylee
.dbo.sp_spaceused"

 

代理表的列有：name,rowtotal,reserved,data,index_size,unused,_objname,_list_indices，其中name,rowtotal,reserved,data,index_size,unused這幾列接收預存程序sp_spaceused返回的結果資料。

注意name,rowtotal,reserved,data,index_size,unused的各列的資料類型要和sp_spaceused返回的列的類型一致。

_objname,_list_indices 是過程sp_spaceused的參數列。

需要再sysservers中加入一個指向自己的遠程伺服器loopback。

sp_addserver loopback,null,@@servername
go

如果@@servername為空白，則寫dsedit中顯示伺服器名。

 

步驟二：在代理表tablespace2上建立視圖，統計庫內所有使用者表的儲存空間

 

create view TableSize(name,rowtotal,reserved,data_KBytes,index_size,unused)
as
select name,cast(str_replace(rowtotal,' KB','') as int),cast(str_replace(reserved,' KB','') as int),cast(str_replace(data,' KB','') as int),cast(str_replace(index_size,' KB','') as int),cast(str_replace(unused,' KB','') as int) from tablespace2 where _list_indices  = 0
and _objname in
     (
        select user_name(uid)+"."+name from andkylee
.dbo.sysobjects where type='U'

     )
go

說明：可以使用其它的條件來僅僅統計某些表，比如：只統計使用者test所擁有的表的空間資訊，那麼改為： where type='U' and uid=user_id("test")

步驟三： 針對視圖TableSize，根據要求寫SQL語句

 

比如：統計記錄數最多的10個表的表名、行數、分配空間，用如下的SQL：

select top 10 name,rowtotal,reserved  from TableSize order by rowtotal desc

go

1> select top 10 name,rowtotal,reserved from TableSize order by rowtotal desc<br />2> go<br /> name<br /> rowtotal reserved<br /> -------------------------------------------------------------------------------<br />-------------------------------------------------<br /> ----------- -----------<br /> SOS_Y_Hs_Xell<br /> 1044911 178308<br /> test3<br /> 1008576 21940<br /> test2<br /> 1008576 21992<br /> SOS_Y_Wharehouse<br /> 380000 31488<br /> SOS_Y_Hs_Xell_copy_1<br /> 288097 49596<br /> FLOWREC1<br /> 285139 40580<br /> PartitionTestTable<br /> 262144 11368<br /> lzftest<br /> 262144 7496<br /> SOS_Y_Hs_Xell_Back<br /> 236000 40512<br /> SOS_R_Wharehouse<br /> 192000 38408<br />(10 rows affected)

 

根據自己的要求寫針對TableSize的相應的SQL語句，上面的sql在此拋磚引玉。

 

備忘：

 

1、由於每次統計過程中都要涉及到待統計表的儲存空間大小的計算，所以，上面介紹的方法對系統效能會有一點小的影響。只要不是頻繁的執行，個人感覺影響可以忽略。

2、另外針對資料庫日誌也就是系統資料表syslogs的統計結果和其它表的不一樣。

1> sp_spaceused syslogs<br />2> go<br /> name total_pages free_pages used_pages<br /> reserved_pages<br /> --------------- --------------- --------------- ---------------<br /> ---------------<br /> syslogs 102400 101945 455<br /> 0<br />(1 row affected)<br />(return status = 0)<br />1>

所以，不要在上面的被統計表中包含syslogs表。