INFORMATION SCHEMA (also referred as system catalog) gives you access to database metadata and MySQL server information like the name of a database or table, the data type of a column, and access privileges.
In this article, we’ll look at how to find a certain column in any Table.
— Searching a column in all views of a specific database.
SELECT DISTINCT A.TABLE_SCHEMA, A.TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES A
JOIN information_schema.COLUMNS B
ON A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_SCHEMA = B.TABLE_SCHEMA
WHERE
A.TABLE_SCHEMA = ‘TestDB’ AND
COLUMN_NAME LIKE ‘%DepartmentID%’;
— Searching a column in all views of all databases.
SELECT DISTINCT A.TABLE_SCHEMA, A.TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES A
JOIN information_schema.COLUMNS B
ON A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_SCHEMA = B.TABLE_SCHEMA
WHERE
COLUMN_NAME LIKE ‘%DepartmentID%’;
Happy learning!!
Big Data & SQL 