INFORMATION_SCHEMA.TABLES & COLUMNS

MySQL 5.x 버전부터는 Information_schema를 지원한다.
드물지만 꼭 필요할때가 가끔있어 블로깅한다.....

※ DB OWNER가 생성한 TABLE만 조회
  - TABLE_TYPE='BASE TABLE'
  - TABLE_TYPE=0x42415345205441424C45

- INFORMATION_SCHEMA.TABLES

INFORMATION_SCHEMA Name	SHOW Name	Remarks
TABLE_CATALOG		NULL
TABLE_SCHEMA	Table_...
TABLE_NAME	Table_...
TABLE_TYPE
ENGINE	Engine	MySQL extension
VERSION	Version	The version number of the table's .frm file, 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

※ 참고 URL : http://dev.mysql.com/doc/refman/5.0/en/tables-table.html


- INFORMATION_SCHEMA.COLUMNS

INFORMATION_SCHEMA Name	SHOW Name	Remarks
TABLE_CATALOG		NULL
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME	Field
ORDINAL_POSITION		see notes
COLUMN_DEFAULT	Default
IS_NULLABLE	Null
DATA_TYPE	Type
CHARACTER_MAXIMUM_LENGTH	Type
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION	Type
NUMERIC_SCALE	Type
CHARACTER_SET_NAME
COLLATION_NAME	Collation
COLUMN_TYPE	Type	MySQL extension
COLUMN_KEY	Key	MySQL extension
EXTRA	Extra	MySQL extension
PRIVILEGES	Privileges	MySQL extension
COLUMN_COMMENT	Comment	MySQL extension

※ 참고 URL : http://dev.mysql.com/doc/refman/5.0/en/columns-table.html