October 9, 2011 at 7:34 pm
Good evening
is there any command to list out the 5 greater tables (size) in a database, aditionally tht list out its attributtes like rows number, etc.
Id appreciate your help
October 9, 2011 at 11:41 pm
modified code of sp_spaceusage
select
t.name,
rows = convert(char(11), rows),
--reserved: reserved
reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
--data: dpages+ used
data = ltrim(str((dpages + used) * d.low / 1024.,15,0) +
' ' + 'KB'),
--index: indexp - data
index_size = ltrim(str((indexp - dpages - used) * d.low / 1024.,15,0) +
' ' + 'KB'),
--unused: reserved - indexp
unused = ltrim(str((reserved - indexp) * d.low / 1024.,15,0) +
' ' + 'KB')
from (
select
object_name(id) name
, rows
, sum(isnull(cast(case when indid in (0, 1, 255) then reserved end as bigint), 0)) reserved
, sum(isnull(cast(case when indid < 2 then dpages end as bigint), 0)) dpages
, sum(isnull(cast(case when indid = 255 then used end as bigint), 0)) used
, sum(isnull(cast(casewhen indid in (0, 1, 255) then usedend as bigint), 0)) indexp
from
sysindexes
where
indid in (0, 1, 255)
group by
id, rows) t, master.dbo.spt_values d
where
d.number = 1
and d.type = 'E'
order by
indexp desc
I Have Nine Lives You Have One Only
THINK!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply