Knowledge Walls
John Peter
Pune, Maharashtra, India
How to count no of columns in a table in mysql?
1204 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;
Previous Topics
Previous lessons of current book.
Computer software engineer articles of One day One Thing to Know
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