Sybase Storage정보

-- 1. DB 별

SELECT  "HOST명"=@@servername,     
    "디바이스명" = substring(d.name, 1, 20),     
    "물리적디바이스명"=d.phyname,      
    "DB명" = db_name(u.dbid),     
    "할당(MB)" = str(round(u.size /(512.0/(@@maxpagesize/@@pagesize)),2),12,1),     
    "가용(MB)"=  str(round(curunreservedpgs(u.dbid,u.lstart,u.unreservedpgs)/ (512.0/(@@maxpagesize/@@pagesize)),2),12,1) ,     
    "사용율(%)"= str(round(  ((u.size-curunreservedpgs(u.dbid,u.lstart,u.unreservedpgs))*100.0/u.size),2),12,1) ,     
    "SegMap설명" = case segmap when 4 then "LOG"     
                   else "DATA" end,   
    convert(varchar(8),u.crdate,112) as crdate   
FROM   master.dbo.sysdevices d,      
     master.dbo.sysusages u      
WHERE  u.vdevno = d.vdevno

 

-- 2. device 별

SELECT  "HOST명"=@@servername,     
    "디바이스명" = substring(d.name, 1, 20),     
    "물리적디바이스명"=d.phyname,      
    "DB명" = db_name(u.dbid),     
    "할당(MB)" = str(round(u.size /(512.0/(@@maxpagesize/@@pagesize)),2),12,1),     
    "가용(MB)"=  str(round(curunreservedpgs(u.dbid,u.lstart,u.unreservedpgs)/ (512.0/(@@maxpagesize/@@pagesize)),2),12,1) ,     
    "사용율(%)"= str(round(  ((u.size-curunreservedpgs(u.dbid,u.lstart,u.unreservedpgs))*100.0/u.size),2),12,1) ,     
    "SegMap설명" = case segmap when 4 then "LOG"     
                   else "DATA" end,   
    convert(varchar(8),u.crdate,112) as crdate   
FROM   master.dbo.sysdevices d,      
     master.dbo.sysusages u      
WHERE   u.vdevno = d.vdevno

 

 -- 3. 테이블 사이즈

  select convert(varchar(30),o.name) as table_name,
    row_count(db_id(), o.id) as row_count,
    data_pages(db_id(), o.id) as pages,
    (data_pages(db_id(), o.id)*(@@maxpagesize/1024))/1024 as MB
 from sysobjects o
where type='U'
and name='테이블명'
order by table_name