タイトル: カラム定義の確認
INFORMATION_SCHEMA.COLUMNSテーブルにカラム定義が格納されている。
| mysql> DESC INFORMATION_SCHEMA.COLUMNS; +--------------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+---------------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | NO | | | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | COLUMN_NAME | varchar(64) | NO | | | | | ORDINAL_POSITION | bigint(21) unsigned | NO | | 0 | | | COLUMN_DEFAULT | longtext | YES | | NULL | | | IS_NULLABLE | varchar(3) | NO | | | | | DATA_TYPE | varchar(64) | NO | | | | | CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES | | NULL | | | CHARACTER_OCTET_LENGTH | bigint(21) unsigned | YES | | NULL | | | NUMERIC_PRECISION | bigint(21) unsigned | YES | | NULL | | | NUMERIC_SCALE | bigint(21) unsigned | YES | | NULL | | | DATETIME_PRECISION | bigint(21) unsigned | YES | | NULL | | | CHARACTER_SET_NAME | varchar(32) | YES | | NULL | | | COLLATION_NAME | varchar(32) | YES | | NULL | | | COLUMN_TYPE | longtext | NO | | NULL | | | COLUMN_KEY | varchar(3) | NO | | | | | EXTRA | varchar(30) | NO | | | | | PRIVILEGES | varchar(80) | NO | | | | | COLUMN_COMMENT | varchar(1024) | NO | | | | +--------------------------+---------------------+------+-----+---------+-------+ |
例えば、TESTと名の付くカラムを持つテーブルを検索したい場合は、以下のコマンドを実行する。
| SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%TEST%'; |