使用一个查询获取数据库模式？ [英] Get database schema with one query?

问题描述

基本上，我想从连接的当前数据库获取表名称和每个表的字段名称。

这是可能吗？ / p>

我知道 SHOW TABLES FROM my_database 获取表名称， SHOW COLUMNS FROM my_table 会得到你的字段，但是这至少[1 x＃的表]查询，我得到更多的信息，我想：）

解决方案

INFORMATION_SCHEMA.COLUMNS 表格有你要求的。

  SELECT table_name，column_name 
 FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE table_schema ='YourDBName'
 ORDER BY table_name，ordinal_position 
  

Basically I want to get the table names, and the field names for each table from the current database that is connected, nothing else.

Is this possible?

I know that SHOW TABLES FROM my_database gets you the table names, and SHOW COLUMNS FROM my_table will get you the fields, but that's at least [1 x # of tables] queries and I get more information that I want :)

解决方案

The INFORMATION_SCHEMA.COLUMNS table has what you're asking for.

SELECT table_name, column_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = 'YourDBName'
    ORDER BY table_name, ordinal_position

这篇关于使用一个查询获取数据库模式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！