Hi Rafal,

How about this code??

use [YourDatabase];

select

a1.object_id

,a2.name

, a1.used_page_count * 8 as total_used_pages

, cast(a1.used_page_count * 8 /1000 as varchar(10)) + 'MB' as SIZE_total_used_pages

, a1.reserved_page_count * 8 as total_reserved_pages

, a1.row_count

from sys.dm_db_partition_stats a1

inner join sys.all_objects a2 on ( a1.object_id = a2.object_id )

left outer join sys.indexes a3 on ( (a1.object_id = a3.object_id) and (a1.index_id = a3.index_id) )

where (select count(distinct partition_number)

from sys.dm_db_partition_stats a4

where (a4.object_id = a1.object_id)) >= 1 and a2.type <>'S'

--and (a1.used_page_count * 8 /1000) > 1 --Uncomment this line to list tables that occupy >1MB space

order by a1.used_page_count desc, a2.name asc, a1.index_id, a1.partition_number

I have not compared my code as against yours.. just thought of putting it up here.. so did I. 😀

Will be glad if you replied on its worth!!! 🙂

Thanks,

Khilan