How do I get the name and attributes of tables from a MySQL database?

31

How to get the names of all MySQL tables ?

How to get the attributes (name, type, etc ...) of a given table in the MySQL database?

    
asked by anonymous 11.12.2013 / 18:08

5 answers

42

To view the tables in a database:

SHOW TABLES;

To view the table structure with name, type, etc.:

DESC nome_da_tabela;
    
11.12.2013 / 18:10
15

You can use the query:

SELECT * FROM information_schema.tables WHERE table_schema = 'nome-do-banco';

It shows the names of the tables and also information like the engine used, creation date, etc.

    
11.12.2013 / 18:15
6

Correct is to query the INFORMATION_SCHEMA database. You'll get data like the type of tables, avg the size of the records, plus the names and the Storage Engine.

DESC INFORMATION_SCHEMA.TABLES;

It will give you a good knowledge of what you can look for per table. Remembering that TODO SHOW command has correlation with tables in INFORMATION_SCHEMA

    
13.12.2013 / 02:18
6

Taking the data from the columns of a given table:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '<tabela>';
    
09.12.2015 / 09:03
2
select * from all_tables where tables like '%tabela desejada%'
    
12.12.2013 / 16:57