Given a column name how can I find which tables in a MySQL database contain that column?
Use the COLUMN_NAME to find which table in a database contains a specific column. Let us first create a table −
mysql> create table DemoTable
-> (
-> CustomerId int,
-> CustomerName varchar(20),
-> CustomerCountryName varchar(100)
-> );
Query OK, 0 rows affected (1.05 sec)
Following is the query to find in which tables a specific column “'CustomerCountryName'” is present −
mysql> select *from information_schema.columns WHERE COLUMN_NAME =
'CustomerCountryName';
This will produce the following output −
+---------------+--------------+--------------+---------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+--------------+------------+-------+---------------------------------+----------------+-----------------------+-------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME |ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE |CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |NUMERIC_PRECISION | UMERIC_SCALE | DATETIME_PRECISION |CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | GENERATION_EXPRESSION | SRS_ID |
+---------------+--------------+--------------+---------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+--------------+------------+-------+---------------------------------+----------------+-----------------------+-------+
| def | sample | demotable189 | CustomerCountryName | 4 | NULL |YES | varchar | 100 | 300 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(100) | | |select,insert,update,references | | | NULL |
| def | web | DemoTable | CustomerCountryName | 4 | NULL |YES | varchar | 20 | 60 | NULL | NULL |NULL | utf8 | utf8_unicode_ci | varchar(20) | | |select,insert,update,references | | | NULL |
| def | web | DemoTable | CustomerCountryName | 3 | NULL |YES | varchar | 100 | 300 | NULL | NULL |NULL | utf8 | utf8_unicode_ci | varchar(100) | | |select,insert,update,references | | | NULL |
+---------------+--------------+--------------+---------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+--------------+------------+-------+---------------------------------+----------------+-----------------------+-------+
3 rows in set (0.68 sec)
Above, you can see the column “CustomerCountryName” is present in 3 tables.
Published on 12-Jul-2019 14:03:41
