Hello William
If you are using MySQL version 5.0 and up, you can select from the
TABLE table in the INFORMATION_SCHEMA database. This database
contains information about all of the structures in the MySQL server.
For example, if you are looking for all of the tables in a database
called PHXTC, you could do this:
mysql select table_name from information_schema.tables where
table_schema='phxtc';
++
| table_name |
++
| accounts |
| check_items|
| checks |
| discounts |
| employees |
| familygroups |
| items |
| items_accounts |
| majorgroups|
| periods|
| refs |
| rev_period |
| revenuecenters |
| servicecharges |
| t |
| taxclass_rates |
| taxclasses |
| taxrates |
| tendermedia|
++
19 rows in set (0.11 sec)
Note that there is quite a lot of other useful information in
INFORMATION_SCHEMA also. In this example i ust selected table names
from one database. You can do this to see all of the tables in
INFORMATION_SCHEMA (or any other MySQL database):
mysql show tables from information_schema;
+---+
| Tables_in_information_schema |
+---+
| CHARACTER_SETS|
| COLLATIONS|
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| KEY_COLUMN_USAGE |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS|
| TABLES|
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---+
16 rows in set (0.00 sec)
And then to see specific information about one of these structures,
the MySQL-specific command is quite useful:
mysql describe tables;
+-+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+-+--+--+-+-+---+
| TABLE_CATALOG | varchar(512) | YES | | | |
| TABLE_SCHEMA| varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | | |
| VERSION | bigint(21) | YES | | | |
| ROW_FORMAT | varchar(10) | YES | | | |
| TABLE_ROWS | bigint(21) | YES | | | |
| AVG_ROW_LENGTH | bigint(21) | YES | | | |
| DATA_LENGTH | bigint(21) | YES | | | |
| MAX_DATA_LENGTH | bigint(21) | YES | | | |
| INDEX_LENGTH| bigint(21) | YES | | | |
| DATA_FREE | bigint(21) | YES | | | |
| AUTO_INCREMENT | bigint(21) | YES | | | |
| CREATE_TIME | datetime | YES | | | |
| UPDATE_TIME | datetime | YES | | | |
| CHECK_TIME | datetime | YES | | | |
| TABLE_COLLATION | varchar(64) | YES | | | |
| CHECKSUM| bigint(21) | YES | | | |
| CREATE_OPTIONS | varchar(255) | YES | | | |
| TABLE_COMMENT | varchar(80) | NO | | | |
+-+--+--+-+-+---+
21 rows in set (0.07 sec)
Good luck!
Douglas Sims
[EMAIL PROTECTED]
On Jun 7, 2006, at 3:19 PM, William Scott wrote:
Dear Sir,
I have a database with over 80 tables. Is there an easy way to
find table names that has PERSON_ID field using SELECT query?
Thanks in advance for any help.
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
