SQL query to find column from all tables of database

SQL query to find a column from all tables of the database.

SQL Query to Find Column From All Tables of Database

I just wanted to keep it here just in case if I need it in future.

For an example, we have a database named cakephp_eshop. And we want to search for a column firstname in all the table of the database. We can archive this by using the below query.

select table_name,column_name from information_schema.columns where column_name like '%firstname%' and table_schema = 'cakephp_eshop';

In this query, we are using – like '%firstname%'. So it will find for firstname and other columns like firstname_user, user_firstname etc… We can use equals also. A little change in the above query to get the exact result you want.

select table_name,column_name from information_schema.columns where column_name = 'firstname' and table_schema = 'cakephp_eshop';

And trust me you don’t wanna use below query because it will search in all the databases, not in one you want. And that will take time to fetch data. So if you want to find it in all the database you can use it.
select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME like '%firstname%' order by TABLE_NAME