Search Column Or Field Name In Tables Or Database

Are you looking for a MySQL Query to search for a specific field name or column name, existing in the tables of a database?

Search Column Or Field Name In Tables Or Database

To search a column name or field name within the whole database, following query will be beneficial, as it will be able to retrieve your desired column, existing in the any of the tables of the chosen database:

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('your_column_name')
AND TABLE_SCHEMA='your_db_name';

Please note that, the above query will retrieve all the table names of the chosen database, in which your desired column name exists.

Search Multiple Column Or Field Names In Database

You can also use LIKE operator instead of IN operator, as if you are not very much sure of the column name, or if you want to search multiple column names with similar name, for example, if you want to search for the fields parent_id and parent_name, so you can use the LIKE operator here as below:

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE ('parent%')
AND TABLE_SCHEMA='your_db_name';

or

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE ('%parent%')
AND TABLE_SCHEMA='your_db_name';

Share with:


Leave a Reply

Login with:



Your email address will not be published. Required fields are marked *