MySQL Find Column Name in any Table in Entire Database
Submitted by badzilla on Mon, 08/02/2010 - 19:58
This need cropped up the other day when I was searching Drpual for all tables that contained a column with the name description. I used the command line interface, and my output is captured below.
It frequently happens, particularly when I am using Drupal, I need to find a name of a column that could be in any table in a database. There is a quick and easy way of finding this, and the syntax is:
SELECT table_name, column_name from information_schema.columns WHERE column_name LIKE '%Name%';This need cropped up the other day when I was searching Drpual for all tables that contained a column with the name description. I used the command line interface, and my output is captured below.
/tmp> mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 111
Server version: 5.1.36 SUSE MySQL RPM
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use badzilla;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT table_name, column_name from information_schema.columns WHERE column_name LIKE '%escription%';
+-----------------------------+---------------------------+
| table_name | column_name |
+-----------------------------+---------------------------+
| CHARACTER_SETS | DESCRIPTION |
| PARTITIONS | PARTITION_DESCRIPTION |
| PLUGINS | PLUGIN_DESCRIPTION |
| actions | description |
| aggregator_category | description |
| aggregator_feed | description |
| aggregator_item | description |
| content_node_field_instance | description |
| menu_custom | description |
| menu_router | description |
| node_type | description |
| page_manager_pages | admin_description |
| term_data | description |
| twitter_account | description |
| upload | description |
| views_view | description |
| vocabulary | description |
| wp_links | link_description |
| wp_term_taxonomy | description |
| wp_links | link_description |
| wp_term_taxonomy | description |
| jos_banner | description |
| jos_categories | description |
| jos_menu_types | description |
| jos_sections | description |
| jos_weblinks | description |
| jos_banner | description |
| jos_categories | description |
| jos_menu_types | description |
| jos_sections | description |
| jos_weblinks | description |
| DatabaseInformation | TemplateDescription |
| Events | Description |
| DatabaseInformation | TemplateDescription |
| Events | Description |
| actions | description |
| content_node_field_instance | description |
| dpo_dependency | ddescription |
| dpo_issue | idescription |
| dpo_projectcontent | description |
| dpo_projectreport | description |
| dpo_risk | rdescription |
| menu_custom | description |
| menu_router | description |
| node_type | description |
| term_data | description |
| upload | description |
| views_view | description |
| vocabulary | description |
| actions | description |
| content_node_field_instance | description |
| menu_custom | description |
| menu_router | description |
| node_type | description |
| term_data | description |
| upload | description |
| vocabulary | description |
| actions | description |
| content_node_field_instance | description |
| menu_custom | description |
| menu_router | description |
| node_type | description |
| term_data | description |
| upload | description |
| views_view | description |
| vocabulary | description |
| Event | Description |
| actions | description |
| content_node_field_instance | description |
| menu_custom | description |
| menu_router | description |
| node_type | description |
| page_manager_pages | admin_description |
| term_data | description |
| upload | description |
| views_view | description |
| vocabulary | description |
| jos_banner | description |
| jos_categories | description |
| jos_menu_types | description |
| jos_sections | description |
| jos_weblinks | description |
| jos_banner | description |
| jos_categories | description |
| jos_menu_types | description |
| jos_sections | description |
| jos_simplecal | attached_file_description |
| jos_weblinks | description |
| help_topic | description |
| user_info | Description |
| jos_banner | description |
| jos_categories | description |
| jos_menu_types | description |
| jos_sections | description |
| jos_weblinks | description |
| phpbb_acl_roles | role_description |
| phpbb_reports_reasons | reason_description |
| actions | description |
| menu_custom | description |
| menu_router | description |
| node_type | description |
| term_data | description |
| vocabulary | description |
+-----------------------------+---------------------------+
103 rows in set (0.30 sec)
mysql>