Select all tables containing specific columns

Question

Select all tables containing specific columns

I have about 300 tables in my database. I want table names containing tha column sname, dtcreatedat, dtmodifiedat, ixlastmodifiedby and fstatus. I am currently manually checking each table. What command can you use in mysql for this?

+5

mysql

Manoj nayak Jul 24 '12 at 9:22

source share

1 answer

juergen d · Accepted Answer · 2012-07-24T09:25:22+0000

You can find all tables containing these columns using

select distinct table_name
from information_schema.columns
where column_name in ('sname','dtcreatedat','dtmodifiedat','ixlastmodifiedby','fstatus')
and table_schema = 'your_db_name'

Select all tables containing specific columns

More articles: