Display column quantities in each table

1

As I do for phpmyadmin show me this:

--- tabelas ---

usuarios(5)
clientes(20)
admins(2)

for example using some mysql command or even some phpmyadmin functionality?

    
asked by anonymous 15.04.2014 / 23:15

2 answers

1

To view the tables of a particular Bank in Mysql with the number of columns use the SQL just below:

Generics is the name of the Bank , then change to the name of your bank

SELECT a.TABLE_NAME,
       a.TABLE_SCHEMA, 
       (SELECT count(*) FROM information_schema.COLUMNS b
            WHERE b.TABLE_SCHEMA = a.TABLE_SCHEMA and b.TABLE_NAME = a.TABLE_NAME)
       TABLE_COLUMNS_COUNT  
FROM INFORMATION_SCHEMA.TABLES a        
WHERE a.TABLE_TYPE = 'BASE TABLE' and a.TABLE_SCHEMA = 'generics'

AnotherwouldbewithGroupBy(Obs:willbringtheviewstoo)

SELECTb.TABLE_NAME,b.TABLE_SCHEMA,count(b.TABLE_SCHEMA)TABLE_COLUMNS_COUNTFROMinformation_schema.COLUMNSbWHEREb.TABLE_SCHEMA='generics'GROUPBYb.TABLE_SCHEMA,b.TABLE_NAME

Source: Click here

    
16.04.2014 / 00:09
2

In PHPMyAdmin you just select the database and click on the + ) and it will expand and display all the tables of the selected database, if you click again on the + >) next to the table, will display its fields. See:

If you wanted to do it via MySQL, use the SHOW TABLES command, as your friend NULL has already replied:

SHOW TABLES FROM database;

@edit

You also want to do a record count of each table, is that right? If it is, use the command below:

SELECT TABLE_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'seu_database'
    
15.04.2014 / 23:37