How to list all tables ordered by their size within a SQL Server 2005 database?

Hi, all,

Would please any expert here give me any guidance and advices for how to list all tables sorted by their size within a SQL Server 2005 database?

Thanks a lot in advance for that.

With best regards,

Yours sincerely,

Hi,

A similar kind of query I've seen on a site. In the example given by the author of the query, he usedDynamic Management Views. You can find the original query from here http://www.extremeexperts.com/SQL/Scripts/FindSizeOfTable.aspx

As per your requirement, I have made some changes to the query. Here it is

WITH table_space_usage ( schema_name, table_name, used, reserved, ind_rows, tbl_rows )
AS(SELECT
s.Name
, o.Name
, p.used_page_count * 8
, p.reserved_page_count * 8
, p.row_count
,casewhen i.index_id in( 0, 1 )then p.row_count else 0 end
FROMsys.dm_db_partition_stats p
INNERJOINsys.objectsas o ON o.object_id= p.object_id
INNERJOINsys.schemasas s ON s.schema_id = o.schema_id
LEFTOUTERJOINsys.indexesas i on i.object_id= p.object_idand i.index_id = p.index_id
WHERE o.type_desc ='USER_TABLE' and o.is_ms_shipped = 0)

SELECT t.schema_name
, t.table_name
,sum(t.used)as used_in_kb
,sum(t.reserved)as reserved_in_kb
,sum(t.tbl_rows)as rows

FROM table_space_usage as t

GROUPBY t.schema_name , t.table_name

ORDERBY used_in_kb desc

Hope it will help you

|||

Hi, Neeraj,

Thanks a lot for your very helpful guidance.

With best regards,

Yours sincerely,