CODEWITHSUNDEEP
mysqldatabasefield
jreed121
jreed121

Reputation: 2097

MySQL return table name with field data for all tables in a DB

I was wondering if it were possible to write a query that would return:

Table | Field | Type | Null | Key

For all of the tables in a DB (Table = table name). I could probably figure a way of building an HTML table with php, but I figured I'd ask if there was a way to achieve this with just one query. I'm looking for a similar output to the following:

Users | UserName | varchar(30) | NOT NULL | PRIMARY

Upvotes: 0

Views: 409

Answers (2)

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38775

As unprivileged user, I can get this

-------------------------------------------------------------------------------
describe account
-------------------------------------------------------------------------------
|Field             |Type                            |Null|Key|Default|Extra         |
|account_id        |int(10) unsigned                |NO  |PRI|<NULL> |auto_increment|
|product_cd        |varchar(10)                     |NO  |MUL|<NULL> |              |

from executing "describe " like any other SELECT.

Upvotes: 1

Adam Price
Adam Price

Reputation: 10267

Check out the information_schema views and tables. These can provide the information you're looking for. Specifically, information_schema.tables and information_schema.columns.

Upvotes: 3

Related Questions