Retrieve list of largest tables in a database

Hello,

is there an easy way to retrieve a list of the largest tables in a
given database? I know how to use stored procedures to retrieve the
size of a single table, but I was wondering if there was a query that
could be used to obtain a list of the 20 (for instance) top largest
tables. I know this can be coded on the client side in the application
by retrieving the size of all the tables in a database and then
selecting only the largest n tables, but I was looking for something
more efficient and just a query if possible.

Thanks in advance.

If you don't mind missing out on 100% accuracy,

select top 20  object_name(id),rowc
nt
from sysindexes
where indid in (0,1)
order by rowcnt desc




<ozonefilter@gmail.com> wrote in message
news:1166050186.506361.21040@80g2000cwy.googlegroups.com...
> Hello,
>
> is there an easy way to retrieve a list of the largest tables in a
> given database? I know how to use stored procedures to retrieve the
> size of a single table, but I was wondering if there was a query that
> could be used to obtain a list of the 20 (for instance) top largest
> tables. I know this can be coded on the client side in the application
> by retrieving the size of all the tables in a database and then
> selecting only the largest n tables, but I was looking for something
> more efficient and just a query if possible.
>
> Thanks in advance.
>

Aaron Bertrand [SQL Server MVP]  wrote:
> If you don't mind missing out on 100% accuracy,
>
> select top 20  object_name(id),rowc
nt
> from sysindexes
> where indid in (0,1)
> order by rowcnt desc

Hi Aaron,

thank you for your answer. If I'm not mistaken, the query above would
only provide me with the list of the tables having the greatest
cardinality. While I do not need 100% accuracy, very large tables (in
bytes) may have a relatively small amount of rows, while smaller tables
may have many tiny records. In fact I've tested it against a real
database and only 40% of the top 20 tables seems to be reported. Any
idea how to get a better approximation?

Thanks.

> thank you for your answer. If I'm not mistaken, the query above  would

> only provide me with the list of the tables having the greatest
> cardinality.

Maybe I'm not sure what you mean by "cardinality" because the query I
provided has nothing to do with cardinality.

I may also be confused by what you mean by "largest".  The query I provided
gives you the top 20 tables in terms of # of rows.  If you want those that
are occupying the largest amount of space, maybe try this:



create table #foo
(
[name] sysname,
rows INT,
reserved nvarchar(32),
data nvarchar(32),
index_size nvarchar(32),
unused nvarchar(32)
);
set nocount on;

exec sp_msForEachTable 'INSERT #foo EXEC sp_spaceused ''?'';';

select top 20 *
from #foo
order by
convert(int, replace(reserved, N' KB', '')) desc;

drop table #foo;

Take a look at
http://vyaskn.tripod.com/ sp_show_b...bl
es.htm





<ozonefilter@gmail.com> wrote in message
news:1166050186.506361.21040@80g2000cwy.googlegroups.com...
> Hello,
>
> is there an easy way to retrieve a list of the largest tables in a
> given database? I know how to use stored procedures to retrieve the
> size of a single table, but I was wondering if there was a query that
> could be used to obtain a list of the 20 (for instance) top largest
> tables. I know this can be coded on the client side in the application
> by retrieving the size of all the tables in a database and then
> selecting only the largest n tables, but I was looking for something
> more efficient and just a query if possible.
>
> Thanks in advance.
>

You can use the dpages, used or reserved columns instead of rowcnt, in the s
ysindexes table.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/


<ozonefilter@gmail.com> wrote in message
news:1166055677.196013.32640@l12g2000cwl.googlegroups.com...
> Aaron Bertrand [SQL Server MVP] wrote: 
>
> Hi Aaron,
>
> thank you for your answer. If I'm not mistaken, the query above would
> only provide me with the list of the tables having the greatest
> cardinality. While I do not need 100% accuracy, very large tables (in
> bytes) may have a relatively small amount of rows, while smaller tables
> may have many tiny records. In fact I've tested it against a real
> database and only 40% of the top 20 tables seems to be reported. Any
> idea how to get a better approximation?
>
> Thanks.
>