カラムの情報はinformation_schema.columnsに格納されているので、必要なものだけ絞り込んで取得すればよい。
information_schema.columnsの構造は以下。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
mysql> use information_schema; mysql> desc 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 | | | 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(27) | NO | | | | | PRIVILEGES | varchar(80) | NO | | | | | COLUMN_COMMENT | varchar(1024) | NO | | | | +--------------------------+---------------------+------+-----+---------+-------+ |
例えばhomesteadというデータベースの全テーブルの全カラムの属性を取得したければ以下。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> select table_name, column_name, data_type, column_key from columns where table_schema = 'homestead'; +-----------------+-------------------+-----------+------------+ | table_name | column_name | data_type | column_key | +-----------------+-------------------+-----------+------------+ | migrations | id | int | PRI | | migrations | migration | varchar | | | migrations | batch | int | | | password_resets | email | varchar | MUL | | password_resets | token | varchar | | | password_resets | created_at | timestamp | | | users | id | int | PRI | | users | name | varchar | | | users | email | varchar | UNI | | users | email_verified_at | timestamp | | | users | password | varchar | | | users | remember_token | varchar | | | users | created_at | timestamp | | | users | updated_at | timestamp | | +-----------------+-------------------+-----------+------------+ |
参考サイト
MySQL :: MySQL 5.6 リファレンスマニュアル :: 3.4 データベースとテーブルに関する情報の取得
https://dev.mysql.com/doc/refman/5.6/ja/getting-information.html
mysqlで全テーブルのカラム一覧を見たい – It’s raining cats and dogs.
http://tadasy.hateblo.jp/entry/20130220/1361357082