Select data from “show tables” MySQL query

2019-01-08 16:37发布

Is it possible to select from show tables in MySQL?

SELECT * FROM (SHOW TABLES) AS `my_tables`

Something along these lines, though the above does not work (on 5.0.51a, at least).

13条回答
祖国的老花朵
2楼-- · 2019-01-08 16:49
SELECT * FROM INFORMATION_SCHEMA.TABLES

That should be a good start. For more, check INFORMATION_SCHEMA Tables.

查看更多
神经病院院长
3楼-- · 2019-01-08 16:50

in MySql 5.1 you can try

show tables like 'user%';

output:

mysql> show tables like 'user%';

+----------------------------+

| Tables_in_test (user%) |

+----------------------------+

| user                       |

| user_password              |

+----------------------------+

2 rows in set (0.00 sec)
查看更多
爷的心禁止访问
4楼-- · 2019-01-08 16:57

To count:

SELECT COUNT(*) as total FROM (SELECT TABLE_NAME as tab, TABLES.* FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='database_name' GROUP BY tab) tables;

To list:

SELECT TABLE_NAME as table, TABLES.* FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='database_name' GROUP BY table;
查看更多
手持菜刀,她持情操
5楼-- · 2019-01-08 16:57

Yes, SELECT from table_schema could be very usefull for system administration. If you have lot of servers, databases, tables... sometimes you need to DROP or UPDATE bunch of elements. For example to create query for DROP all tables with prefix name "wp_old_...":

SELECT concat('DROP TABLE ', table_name, ';') FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = '*name_of_your_database*'
AND table_name LIKE 'wp_old_%';
查看更多
姐就是有狂的资本
6楼-- · 2019-01-08 16:58

Have you looked into querying INFORMATION_SCHEMA.Tables? As in

SELECT ic.Table_Name,
    ic.Column_Name,
    ic.data_Type,
    IFNULL(Character_Maximum_Length,'') AS `Max`,
    ic.Numeric_precision as `Precision`,
    ic.numeric_scale as Scale,
    ic.Character_Maximum_Length as VarCharSize,
    ic.is_nullable as Nulls, 
    ic.ordinal_position as OrdinalPos, 
    ic.column_default as ColDefault, 
    ku.ordinal_position as PK,
    kcu.constraint_name,
    kcu.ordinal_position,
    tc.constraint_type
FROM INFORMATION_SCHEMA.COLUMNS ic
    left outer join INFORMATION_SCHEMA.key_column_usage ku
        on ku.table_name = ic.table_name
        and ku.column_name = ic.column_name
    left outer join information_schema.key_column_usage kcu
        on kcu.column_name = ic.column_name
        and kcu.table_name = ic.table_name
    left outer join information_schema.table_constraints tc
        on kcu.constraint_name = tc.constraint_name
order by ic.table_name, ic.ordinal_position;
查看更多
Emotional °昔
7楼-- · 2019-01-08 17:01

You may be closer than you think — SHOW TABLES already behaves a lot like SELECT:

$pdo = new PDO("mysql:host=$host;dbname=$dbname",$user,$pass);
foreach ($pdo->query("SHOW TABLES") as $row) {
    print "Table $row[Tables_in_$dbname]\n";
}
查看更多
登录 后发表回答