SYBASE ASE - Neatly Display DB/Schema Sizes

when running isql it should have "-w999"

​

isql -Usapsa -S -P -X -w999

​

after that here are the commands:

​

use master

​

go

​

declare @pagesize numeric(19,0) select @pagesize=(select @@maxpagesize) SELECT "DB Name" = CONVERT(char(15), db_name(D.dbid)),"DB Size MB" = STR(SUM(CASE WHEN U.segmap != 4 THEN U.size*@pagesize/1048576 END),10,1), "Used Data MB" = STR(SUM(CASE WHEN U.segmap != 4 THEN size - curunreservedpgs(U.dbid, U.lstart, U.unreservedpgs)END)*@pagesize/1048576,10,1), "Data Full%" = STR(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)),9,1), "Log Size MB" = STR(SUM(CASE WHEN U.segmap = 4 THEN U.size*@pagesize/1048576 END),10,1), "Free Log MB" = STR(lct_admin("logsegment_freepages",D.dbid)*@pagesize/1048576,10,1), "Log Full%" = STR(100 * (1 - 1.0 * lct_admin("logsegment_freepages",D.dbid) / SUM(CASE WHEN U.segmap = 4 THEN U.size END)),7,1) FROM master..sysdatabases D, master..sysusages U WHERE U.dbid = D.dbid AND ((D.dbid != 2)) GROUP BY D.dbid ORDER BY db_name(D.dbid)  

(1 liked)

​

go