Table Size vs Database Size

[former_SQLA_member1694868]

What is the best way to determine which tables are the culprits in a large (disk size) database.

We put out the same basic structure of tables to our customers and some databases end up much bigger than others. My initial attempts to determine where the size is used were to check the row counts for each table, but I do not think I am doing that as efficiently as I could, going table by table. But a higher row count does not necessarily mean more space on the disk if another lower row count table is storing more data.

I also checked in Sybase Central the Fragmentation for the tables with the most main pages, as well as the %File column of Page Usage which I assume means the % of the database file that table uses.

So I thought I would ask if there is a more efficient, more accurate way to tell which tables were taking up the most disk space within the database file.

[MCMartin · Accepted solution]

select table_page_count+ext_page_count as totalpages, table_name  from systable order by totalpages desc

The table using the most db pages will be on top 😉

[johnsmirnios]

dbinfo -u is a convenient way to get the information from the command line. It's probably equivalent to the information displayed in Sybase Central. Builds 2630 and later of 12.0.0 as well as all versions of 12.0.1 gather this information much more quickly when it is not already in cache.

[Breck_Carter]

Foxhound shows the amount of disk space used by each table: data pages, extension pages and indexes: How much space?

It also shows a list of the largest tables and materialized views: What's unusual about this database?

The numbers shown are for disk space, not column size; for example, the bytes-per-row number is the total disk space used by the table and all its indexes divided by the row count, and is larger (sometimes much larger) than the bytes-of-column-data-per-row... the latter number might have some use but I haven't found one yet 🙂

Foxhound is designed from the ground up to be the database consultant's friend when first encountering an existing database.