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

avatar 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

avatar image

Kev Riley ♦♦
64k 48 61 81

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.

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:

x109
x15

asked: Oct 27, 2011 at 05:15 AM

Seen: 5001 times

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

Copyright 2016 Redgate Software. Privacy Policy