Get table_schema, Table_name and total number of columns from information_schema in mysql

Question

I need to get table_schema, Table_name and total number of columns from information_schema in mysql (In short all the table names, with no.of columns each table is having in all schemas). Let me know if there someone is having handy.

SELECT CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME) as entity, count(c.COLUMN_NAME)
FROM information_schema.TABLES t JOIN information_schema.STATISTICS s on
s.TABLE_CATALOG = t.TABLE_CATALOG
AND s.TABLE_SCHEMA = t.TABLE_SCHEMA
AND s.TABLE_NAME = t.TABLE_NAME
AND s.INDEX_NAME  = 'PRIMARY'
AND s.COLUMN_NAME = 'PK'
AND s.SEQ_IN_INDEX  = 1
JOIN information_schema.`COLUMNS` c on
c.TABLE_CATALOG = t.TABLE_CATALOG
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
AND c.COLUMN_NAME = 'VERSION'
WHERE 1=1 
AND t.TABLE_NAME LIKE '%\_%'
AND t.TABLE_SCHEMA LIKE '%\_%'
AND BINARY(t.TABLE_NAME) != LOWER(t.TABLE_NAME)

Answer 1

You're overcomplicating it.

SELECT TABLE_NAME, TABLE_SCHEMA, COUNT(*) COLUMNS
  FROM information_schema.COLUMNS
 GROUP BY  TABLE_NAME, TABLE_SCHEMA

You can add all sorts of WHERE clauses etc to this.

And, in StackOverflow it's good to explain what you tried, and what went wrong when you tried it.

Answer 2

I got the solution, if some one is looking for similar one, Question: Get all the table names, and their column count across databases in mysql:

SELECT  CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME) as entity, 
count(c.COLUMN_NAME) FROM information_schema.TABLES t 
JOIN information_schema.STATISTICS s 
on s.TABLE_CATALOG = t.TABLE_CATALOG 
AND s.TABLE_SCHEMA = t.TABLE_SCHEMA 
AND s.TABLE_NAME = t.TABLE_NAME 
AND s.INDEX_NAME  = 'PRIMARY' 
AND s.COLUMN_NAME = 'PK' 
AND s.SEQ_IN_INDEX  = 1 
JOIN information_schema.`COLUMNS` c 
on c.TABLE_CATALOG = t.TABLE_CATALOG AND c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME WHERE 1=1  AND BINARY(t.TABLE_NAME) != LOWER(t.TABLE_NAME) GROUP BY t.TABLE_NAME, t.TABLE_SCHEMA;