Mysql – How to locate all the tables that have a field named username

information-schemaMySQL

Is there a way in PhpMyAdmin to do a SQL search for all tables, that have a fieldname username in it?

I am trying to help a friend that has a database with over 1,000 tables in it.

He needs to find all of them that have username as a field name, so he can find the list of tables and not have to manually look at each table.

I don't know MySQL enough to do it.

Anyone know where to find the way?

Best Answer

I found it...

SELECT table_name, column_name, data_type, ordinal_position 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_schema = 'dbname' AND column_name LIKE '%username%';

Related Solutions

Mysql – How to select using the same fields from an undetermined number of tables using MySQL

You can have the information_schema build the entire query for you and execute it as Dynamic SQL (Prepared Statements as @a1ex07 first mentioned):

SELECT CONCAT('SELECT * FROM (SELECT bar_id,bar_weight FROM ',
GROUP_CONCAT(tb SEPARATOR
' WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM '),
' WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5')
INTO @foo_query FROM
(
    select CONCAT(table_schema,'.',table_name) tb
    from information_schema.tables
    where table_name like 'foo_reference%'
) A;
SELECT @foo_query\G
PREPARE stmt FROM @foo_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

First, let's make some sample tables with sample data:

DROP DATABASE IF EXISTS timpost1;
DROP DATABASE IF EXISTS timpost2;
CREATE DATABASE timpost1;
CREATE DATABASE timpost2;
use timpost1
CREATE TABLE cookie_cutter
(
    foo_id int,
    bar_id int,
    bar_weight int,
    primary key (foo_id)
) ENGINE=MyISAM;
CREATE TABLE timpost1.foo_reference1 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost1.foo_reference2 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost1.foo_reference3 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference1 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference2 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference3 LIKE timpost1.cookie_cutter;
INSERT INTO timpost1.foo_reference1 VALUES (1,17,10),(2,3,20);
INSERT INTO timpost1.foo_reference2 VALUES (1,27,90),(2,3,20);
INSERT INTO timpost1.foo_reference3 VALUES (1,37,40),(2,3,20);
INSERT INTO timpost2.foo_reference1 VALUES (1,47,70),(2,3,20);
INSERT INTO timpost2.foo_reference2 VALUES (1,57,20),(2,3,20);
INSERT INTO timpost2.foo_reference3 VALUES (1,67,50),(2,3,20);

Here are the sample tables being made and loaded:

mysql> DROP DATABASE IF EXISTS timpost1;
Query OK, 4 rows affected (0.03 sec)

mysql> DROP DATABASE IF EXISTS timpost2;
Query OK, 3 rows affected (0.02 sec)

mysql> CREATE DATABASE timpost1;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE timpost2;
Query OK, 1 row affected (0.00 sec)

mysql> use timpost1
Database changed
mysql> CREATE TABLE cookie_cutter
    -> (
    ->     foo_id int,
    ->     bar_id int,
    ->     bar_weight int,
    ->     primary key (foo_id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE timpost1.foo_reference1 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE timpost1.foo_reference2 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE timpost1.foo_reference3 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE timpost2.foo_reference1 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE timpost2.foo_reference2 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE timpost2.foo_reference3 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO timpost1.foo_reference1 VALUES (1,17,10),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost1.foo_reference2 VALUES (1,27,90),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost1.foo_reference3 VALUES (1,37,40),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost2.foo_reference1 VALUES (1,47,70),(2,3,20);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost2.foo_reference2 VALUES (1,57,20),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost2.foo_reference3 VALUES (1,67,50),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>

Now let's build the query and execute it:

mysql> SELECT CONCAT('SELECT * FROM (SELECT bar_id,bar_weight FROM ',
    -> GROUP_CONCAT(tb SEPARATOR
    -> ' WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM '),
    -> ' WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5')
    -> INTO @foo_query FROM
    -> (
    ->     select CONCAT(table_schema,'.',table_name) tb
    ->     from information_schema.tables
    ->     where table_name like 'foo_reference%'
    -> ) A;
Query OK, 1 row affected (0.02 sec)

mysql> SELECT @foo_query\G
*************************** 1. row ***************************
@foo_query: SELECT * FROM (SELECT bar_id,bar_weight FROM timpost1.foo_reference1 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost1.foo_reference2 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost1.foo_reference3 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference1 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference2 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference3 WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5
1 row in set (0.01 sec)

mysql> PREPARE stmt FROM @foo_query;
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> EXECUTE stmt;
+--------+------------+
| bar_id | bar_weight |
+--------+------------+
|     27 |         90 |
|     47 |         70 |
|     67 |         50 |
|     37 |         40 |
|     57 |         20 |
+--------+------------+
5 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql>

CAVEAT

Notice that the query will cross check all databases in the mysql instance that starts with foo_reference. In the above example, both timpost1 and timpost2 were checked.

Also, notice that the subquery will get 6 rows, and the limit 0,5 properly displays the first 5 rows of the subquery.

Give it a Try !!!

Mysql – Human relations between users

You may want to recognize directional relationships (e.g. "is the mother of", "is the son of") or you may need to acknowledge that relationships are not bilateral insofar as the users don't grant each other exactly the same rights. In that case you need to keep two records in users_relations for each relationship. Otherwise, I would just capture each user linkage once.

Union queries take about twice as long as single queries, but when these queries are built on fast operations like index seeks this isn't so bad. In any case you'd be trading this off against twice as much storage, which mitigates the impact slightly.

I'd be much more concerned about the potential headaches of managing the redundant data (if it is indeed redundant).

You could pick a convention like lowest id_user first to make certain kinds of operations more predictable - such as updates and deletes.

Best Answer

Related Solutions

Mysql – How to select using the same fields from an undetermined number of tables using MySQL

CAVEAT

Mysql – Human relations between users

Related Question