Knowledge Walls
John Peter
Pune, Maharashtra, India
How to count no of columns in a table in mysql?
3013 Views
To Get by INFORMATION_SCHEMA.COLUMNS Table
-- Specify database name filter and it is grouped by table_name
-- Ouput will get Table name and no of columns in each table

SELECT
    TABLE_NAME,
    COUNT(*) AS NO_OF_COLUMNS
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = "database_name"
GROUP BY TABLE_NAME;
Output 
TABLE_NAME NO_OF_COLUMNS
userinfo 12
account_info 8
transaction_details 20
Order by no of columns count
-- Ascending
SELECT
    TABLE_NAME,
    COUNT(*) AS NO_OF_COLUMNS
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = "database_name"
GROUP BY TABLE_NAME
ORDER BY COUNT(*) ASC;


-- Descending
SELECT
    TABLE_NAME,
    COUNT(*) AS NO_OF_COLUMNS
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = "database_name"
GROUP BY TABLE_NAME
ORDER BY COUNT(*) DESC;
Best Lessons of "One day One Thing to Know"
Top lessons which are viewed more times.
  Copyright © 2014 Knowledge walls, All rights reserved
KnowledgeWalls
keep your tutorials and learnings with KnowledgeWalls. Don't lose your learnings hereafter. Save and revise it whenever required.
Click here for more details