how to select max top 10 (n) rows with there size?

Question

I already have the below query that gets the size of tables. However I am intersted only to get top 10 max size table with there size.

how to do that ?

select convert(varchar(30),o.name) AS table_name,
row_count(db_id(), o.id) AS row_count,
data_pages(db_id(), o.id, 0) AS pages,
data_pages(db_id(), o.id, 0) * (@@maxpagesize/1024) AS kbs
from sysobjects o
where type = 'U'
order by table_name

searching for similar: select top 10 max(datapages) from sysobjects

edit:

Also now I have to get the size of indexes, adding sysindexes to the query is enough or should add another system table like syscoments ?

writing it such way is giving me top table name:

select top 10 convert(varchar(30),o.name) AS table_name,
row_count(db_id(), o.id) AS row_count,
data_pages(db_id(), o.id, 0) AS pages,
data_pages(db_id(), o.id, 0) * (@@maxpagesize/1024) AS kbs
from sysobjects o
where type = 'U'
order by table_name , kbs

this is throwing error

select top 10 data_pages(db_id(), o.id, 0) * (@@maxpagesize/1024) AS kbs ,
convert(varchar(30),o.name) AS table_name,
row_count(db_id(), o.id) AS row_count,
data_pages(db_id(), o.id, 0) AS pages
from sysobjects o
where type = 'U'
order by  kbs , table_name 

Answer 1

This Query works on my ASE 16.0 system:

select top 10 convert(varchar(30),o.name) AS table_name,
row_count(db_id(), o.id) AS row_count,
data_pages(db_id(), o.id, 0) AS pages,
data_pages(db_id(), o.id, 0) * (@@maxpagesize/1024) AS kbs
from sysobjects o
where type = 'U'
order by kbs DESC, table_name ASC