[?] How to Calculate Data Size in MySQL Tables [?]

[baden]

In PostgreSQL you can access meta-data about table names, etc. so you could write a program that will automatically scan through all the tables in a database.

How do you do this with MySQL?

Here's the scenerio: Want to scan through all the tables in a db, finding data that matches a specifc key value (ie. a specific user), and calculate what percentage of space (and total space) is being used by those matched (that user).

 

[BNPMike]

Can I widen this question to ask Where is the MySQL Cataologue documented?

The on-line documentaion is limited:

What if you forget the name of a database or table, or what the structure of a given table is (for example, what its columns are called)? MySQL addresses this problem through several statements that provide information about the databases and tables it supports.

You have already seen SHOW DATABASES, which lists the databases managed by the server. To find out which database is currently selected, use the DATABASE() function:

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie |
+------------+
If you haven't selected any database yet, the result is NULL (or the empty string before MySQL 4.1.1).

To find out what tables the current database contains (for example, when you're not sure about the name of a table), use this command:

mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| event |
| pet |
+---------------------+
If you want to find out about the structure of a table, the DESCRIBE command is useful; it displays information about each of a table's columns:

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
Field indicates the column name, Type is the datatype for the column, NULL indicates whether the column can contain NULL values, Key indicates whether the column is indexed, and Default specifies the column's default value.

If you have indexes on a table, SHOW INDEX FROM tbl_name produces information about them

 

[baden]

Oh, and one thing to add to this ... how to do this from code. Getting the data is easy from the mySQL prompt, but this needs to be done as SELECT statements as to return the data for processing (ie. from Java, .Net, etc)
(wow, Java and .Net so close together - they may fight)!