x

how to get top 10 tables in size in a database in a sql server ?

top 10 tables
more ▼

asked Oct 27, 2011 at 05:15 AM in Default

Srishiva gravatar image

Srishiva
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Right click a database in Object Explorer in SSMS, choose Reports -> Disk Usage by Top Tables

---- edit

TSQL run to generate this report

begin try 
    select top 1000
            ( row_number() over ( order by ( a1.reserved + isnull(a4.reserved,
                                                              0) ) desc ) )
            % 2 as l1 ,
            a3.name as [schemaname] ,
            a2.name as [tablename] ,
            a1.rows as row_count ,
            ( a1.reserved + isnull(a4.reserved, 0) ) * 8 as reserved ,
            a1.data * 8 as data ,
            ( case when ( a1.used + isnull(a4.used, 0) ) > a1.data
                   then ( a1.used + isnull(a4.used, 0) ) - a1.data
                   else 0
              end ) * 8 as index_size ,
            ( case when ( a1.reserved + isnull(a4.reserved, 0) ) > a1.used
                   then ( a1.reserved + isnull(a4.reserved, 0) ) - a1.used
                   else 0
              end ) * 8 as unused
    from    ( select    ps.object_id ,
                        sum(case when ( ps.index_id < 2 ) then row_count
                                 else 0
                            end) as [rows] ,
                        sum(ps.reserved_page_count) as reserved ,
                        sum(case when ( ps.index_id < 2 )
                                 then ( ps.in_row_data_page_count
                                        + ps.lob_used_page_count
                                        + ps.row_overflow_used_page_count )
                                 else ( ps.lob_used_page_count
                                        + ps.row_overflow_used_page_count )
                            end) as data ,
                        sum(ps.used_page_count) as used
              from      sys.dm_db_partition_stats ps
              group by  ps.object_id
            ) as a1
            left outer join ( select    it.parent_id ,
                                        sum(ps.reserved_page_count) as reserved ,
                                        sum(ps.used_page_count) as used
                              from      sys.dm_db_partition_stats ps
                                        inner join sys.internal_tables it on ( it.object_id = ps.object_id )
                              where     it.internal_type in ( 202, 204 )
                              group by  it.parent_id
                            ) as a4 on ( a4.parent_id = a1.object_id )
            inner join sys.all_objects a2 on ( a1.object_id = a2.object_id )
            inner join sys.schemas a3 on ( a2.schema_id = a3.schema_id )
    where   a2.type <> N'S'
            and a2.type <> N'IT'
end try
begin catch
    select  -100 as l1 ,
            1 as schemaname ,
            error_number() as tablename ,
            error_severity() as row_count ,
            error_state() as reserved ,
            error_message() as data ,
            1 as index_size ,
            1 as unused
end catch
more ▼

answered Oct 27, 2011 at 05:18 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.2k 47 49 76

hi kev reley thanks for immediate respond ... and any other queries to get this output?
Oct 27, 2011 at 06:34 AM Srishiva
@Srishiva : I've updated my answer with the code that runs this report. (I used trace to capture this)
Oct 27, 2011 at 06:38 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x84
x13

asked: Oct 27, 2011 at 05:15 AM

Seen: 3400 times

Last Updated: Oct 27, 2011 at 05:15 AM