How to count the number of columns in a table?

Here is the generic syntax for counting the number of columns in MySQL table:

SELECT
  COUNT(*)
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  table_name = 'table_name';

If you have multiple databases with the same table name, you should specify the database:

SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'database_name'
AND table_name = 'table_name';

Example, count the number of columns from the table film in database sakila:

SELECT
  COUNT(*)
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  table_schema = 'sakila'
  AND table_name = 'film';

TablePlus is a native database client. You can work with Oracle, MySQL, SQL Server, PostgreSQL, and other databases side by side in the same app.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS

TablePlus in Dark mode

← All posts