Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

23.2. The INFORMATION_SCHEMA TABLES Table

The TABLES table provides information about tables in databases.

INFORMATION_SCHEMA Name SHOW Name Remarks
TABLE_CATALOG   NULL
TABLE_SCHEMA Table_...  
TABLE_NAME Table_...  
TABLE_TYPE    
ENGINE Engine MySQL extension
VERSION Version MySQL extension
ROW_FORMAT Row_format MySQL extension
TABLE_ROWS Rows MySQL extension
AVG_ROW_LENGTH Avg_row_length MySQL extension
DATA_LENGTH Data_length MySQL extension
MAX_DATA_LENGTH Max_data_length MySQL extension
INDEX_LENGTH Index_length MySQL extension
DATA_FREE Data_free MySQL extension
AUTO_INCREMENT Auto_increment MySQL extension
CREATE_TIME Create_time MySQL extension
UPDATE_TIME Update_time MySQL extension
CHECK_TIME Check_time MySQL extension
TABLE_COLLATION Collation MySQL extension
CHECKSUM Checksum MySQL extension
CREATE_OPTIONS Create_options MySQL extension
TABLE_COMMENT Comment MySQL extension

Notes:

  • TABLE_SCHEMA and TABLE_NAME are a single field in a SHOW display, for example Table_in_db1.

  • TABLE_TYPE should be BASE TABLE or VIEW. If table is temporary, then TABLE_TYPE = TEMPORARY. (There are no temporary views, so this is not ambiguous.)

  • The TABLE_ROWS column is NULL if the table is in the INFORMATION_SCHEMA database. For InnoDB tables, the row count is only a rough estimate used in SQL optimization.

  • We have nothing for the table's default character set. TABLE_COLLATION is close, because collation names begin with a character set name.

The following statements are equivalent:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
  [WHERE table_schema = 'db_name']
  [WHERE|AND table_name LIKE 'wild']

SHOW TABLES
  [FROM db_name]
  [LIKE 'wild']

 
 
  Published under the terms of the GNU General Public License Design by Interspire