Sybase
在 Sybase 中一次獲取所有數據庫的數據庫使用情況
如何一次獲取所有數據庫的數據庫使用情況?
sp_spaceused
並且sp_helpdb <dbname>
一次只給出一個用法。我使用 Sybase Central 客戶端。
下面是我使用的腳本—
master..sysdatabases
&master..sysusages
--- Source : http://benohead.com/sybase-size-of-data-and-log-segments-for-all-databases/ select db_name(d.dbid) as db_name, ceiling(sum(case when u.segmap != 4 then u.size/1048576.*@@maxpagesize end )) as data_size, ceiling(sum(case when u.segmap != 4 then size - curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end)/1048576.*@@maxpagesize) as data_used, ceiling(100 * (1 - 1.0 * sum(case when u.segmap != 4 then curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end) / sum(case when u.segmap != 4 then u.size end))) as data_used_pct, ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end)) as log_size, ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end) - lct_admin("logsegment_freepages",d.dbid)/1048576.*@@maxpagesize) as log_used, ceiling(100 * (1 - 1.0 * lct_admin("logsegment_freepages",d.dbid) / sum(case when u.segmap in (4, 7) then u.size end))) as log_used_pct from master..sysdatabases d, master..sysusages u where u.dbid = d.dbid and d.status not in (256,4096) group by d.dbid order by db_name(d.dbid)
您可能應該創建一個 .sql 腳本,該腳本可以執行並返回多個命令的值。
可以使用以下格式輕鬆創建腳本:
set nocount on select "sp_helpdb " + name + char(10) + "exec " + name + "..sp_spaceused" + char(10) + "go" go
這將列印出如下所示的命令列表:
sp_helpdb DATABASE_01 exec DATABASE..sp_spaceused go sp_helpdb DATABASE_02 exec DATABASE..sp_spaceused go ...
可以將此輸出擷取到腳本文件中,然後一次性執行。