Get all tables size in one output table

,

It`s like sp_spaceused, without any kind of cursor.

SELECT         
sysusers.name as TABLE_USER,  
sysobjects.name as TABLE_NAME,     
sum(sysindexes.rowcnt) as ROWS,  
sum(case when sysindexes.indid in (0, 1, 255) then convert(dec(15),reserved*8)else 0 end) as RESERVED_Kb,    
sum(case when sysindexes.indid < 2 THEN convert(dec(15),dpages*8) ELSE 0 END)/+     
sum(case when sysindexes.indid = 255 THEN convert(dec(15),used*8) ELSE 0 END) AS DATA_Kb,    
  
(sum(case when sysindexes.indid in (0, 1, 255) then convert(dec(15),used*8) ELSE 0 END) -  
sum(case when sysindexes.indid < 2 THEN convert(dec(15),dpages*8) ELSE 0 END) -  
sum(case when sysindexes.indid = 255 THEN convert(dec(15),used*8) ELSE 0 END)) AS INDEX_SIZE_Kb,    
  
sum(case when sysindexes.indid in (0, 1, 255) then convert(dec(15),(reserved - used)*8) else 0 end) AS UNUSED_Kb  
  
FROM         sysobjects INNER JOIN    
                      sysindexes ON sysobjects.id = sysindexes.id    
  INNER JOIN sysusers ON sysobjects.uid = sysusers.uid  
WHERE     (sysobjects.type = 'U')     
group by sysusers.name, sysobjects.name

Rate

Share

Share

Rate