find tables having fields matching a criteria

[sfullman]

Hi,

Have a MYSQL db with many tables (100+), and want to select tables which have field names matching a certain criteria. Is there a way to say:

SELECT tables WITH fieldNames LIKE '%username%'

??

In MS SQL you can look up sysobjects but I'm not sure how to do that in MYSQL.

Thanks,
Sam

[MrPixel]

You'll probably have to write a script and run the following query on each table:

SHOW COLUMNS FROM table_name FROM database_name LIKE '%column_name%';

Get a list of your tables with:

SHOW TABLES FROM database_name