Couple Search questions in MySQL

[jbradley04]

I have a few questions:

1. Can you search a whole DB for tables that contain a certain field? For example Search DB1 for any table that contains the field username?

 

2. How can I go about changing the prefix on every table name without indivudually doing it? ie. ex_table1, ex_table2, ex_table3, ex_table4 and change this to ie_table1, ie_table2, ie_table3, ie_table4

 

I appreciate the help!

[requinix]

1. information_schema contains a number of tables which you may find useful, such as TABLES.

 

2. Easiest way would be to use SQL to construct a series of RENAME TABLE statements (also using information_schema). Then run those statements. I mean like

SELECT CONCAT("RENAME TABLE `", t.TABLE_SCHEMA, "`.`", t.TABLE_NAME, "` TO `", new name for the table, "`;") FROM information_schema.TABLES...

Or perhaps easier would be to make a PHP script to pull the table names and execute a RENAME TABLE for each.

[Christian F.]

What I'm really curious about is why you want to do this? If you explain what you want to do, I'm almost certain we can suggest another (and better) alternative.