Select top 10

         name   as TableName

         ,sum(Rowcnt)   as table_rows

      ,sum(ReservedKb) as tablesize_kb

         ,sum(TableUsedKb)   as datasize_kb

      ,sum(UsedKb-TableUsedKb) as indexsize_kb

         ,sum(TMPTABLESNum)   as tmptable_num

From

(select

      object_id

      ,schema_id

      ,case when name like 'TMP%' then 'TMPTABLES' else name end name

         ,case   when name like 'TMP%' then 1 else 0 end TMPTABLESNum

      ,(Select max(row_count) from sys.dm_db_partition_stats p with(nolock)   where p.object_id=t.object_id and p.index_id < 2)  as Rowcnt

      ,(Select Count(1) from dbo.syscolumns with(nolock) where id =   t.object_id) as Columns

      ,(Select Count(distinct index_id) from sys.dm_db_partition_stats p   with(nolock) where p.object_id=t.object_id) as Indexes

      ,(SELECT SUM(length) FROM dbo.syscolumns with(nolock) WHERE id =   t.object_id) as RowLength

      ,IsNull((Select SUM(reserved_page_count) from   sys.dm_db_partition_stats p with(nolock) where p.object_id=t.object_id),0)*8

        + IsNull((Select   sum(reserved_page_count)

                FROM sys.dm_db_partition_stats   p2 with(nolock)

                inner join   sys.internal_tables it with(nolock) on p2.object_id = it.object_id

                WHERE it.parent_id =   t.object_id

                        AND it.internal_type   IN (202,204,207,211,212,213,214,215,216,221,222,236)),0)* 8 as ReservedKb

      ,IsNull((Select SUM(in_row_data_page_count + lob_used_page_count +   row_overflow_used_page_count)

                 from   sys.dm_db_partition_stats p with(nolock) where p.object_id=t.object_id and   p.index_id < 2),0)* 8 as TableUsedKb

      ,IsNull((Select SUM(used_page_count) from sys.dm_db_partition_stats p   with(nolock) where p.object_id=t.object_id),0)*8

        + IsNull((Select sum(used_page_count)

                FROM   sys.dm_db_partition_stats p2 with(nolock)

                inner join   sys.internal_tables it with(nolock) on p2.object_id = it.object_id

                WHERE it.parent_id =   t.object_id

                        AND it.internal_type   IN (202,204,207,211,212,213,214,215,216,221,222,236)),0)* 8 as UsedKb

      ,create_date

      ,modify_date

from sys.tables t with(nolock)

where Type='U'

) A

group by name

order by 3 desc