roy ashbrook

this is a repost of something i wrote a long time ago. there are already stored procs to view table sizes on a specific table. this was just something that i wrote so that i could create a list of all of the tables in the current database and change the sort orders if i wanted. it also gave me the ability to filter based on table sizes if i needed to only see tables in a certain range.

sorry the code formatting is lame. blame the new version of community server. 

-- view all table sizes

-- author: royashbrook - royashbrook@yahoo.com

-- april 2005

select

      so.id as [OBJECT_ID],

      so.name as [OBJECT_NAME],

      coalesce(j_rows.rows,0) as [ROWCOUNT],

      coalesce(j_ru.sum_reserved,0) * cast(m.low as dec) / 1024 as [RESERVED (KB)],

      d.data * cast(m.low as dec) / 1024 as [DATA (KB)],

      (coalesce(j_ru.sum_used,0) - d.data) * cast(m.low as dec) / 1024 as [INDEX (KB)],

      (coalesce(j_ru.sum_reserved,0) - coalesce(j_ru.sum_used,0)) * cast(m.low as dec) / 1024 as [UNUSED (KB)]

from

      sysobjects so

      join master.dbo.spt_values m

            on m.number = 1 and m.type = 'E'

      -- rows

      left join sysindexes j_rows

            on j_rows.indid < 2 and j_rows.id = so.id

      -- reserved: sum(reserved) where indid in (0, 1, 255)

      -- index: sum(used) where indid in (0, 1, 255) - data

      -- unused: sum(reserved) - sum(used) where indid in (0, 1, 255)

      left join

      (

            select

                  id

                  , sum(reserved) as sum_reserved

                  , sum(used) as sum_used

            from

                  sysindexes

            where

                  indid in (0, 1, 255)

            group by

                  id

      ) j_ru

            on j_ru.id = so.id

      -- data: sum(dpages) where indid < 2

      -- + sum(used) where indid = 255 (text)

      left join

      (

            select

                  j_dpages.id

                  , coalesce(j_dpages._sum,0) +

                        coalesce(j_used._sum,0) [data]

            from

                  (

                        select

                              id

                              , sum(dpages) [_sum]

                        from

                              sysindexes

                        where

                              indid < 2

                        group by

                        id

                  ) j_dpages

                  left join

                  (

                        select

                              id

                              , sum(used) [_sum]

                        from

                              sysindexes

                        where

                              indid = 255

                        group by

                              id

                  ) j_used

                  on j_used.id = j_dpages.id

            ) d

            on d.id = so.id

where

      objectproperty(so.id, N'IsUserTable') = 1

order by

      [DATA (KB)] DESC, [ROWCOUNT] ASC