Find the number of columns in a table

SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_catalog = 'database_name' -- the database
   AND table_name = 'table_name'

Or use the sys.columns

--SQL 2005
SELECT  *
FROM    sys.columns
WHERE   OBJECT_NAME(object_id) = 'spt_values'
-- returns 6 rows = 6 columns

--SQL 2000
SELECT  *
FROM    syscolumns
WHERE   OBJECT_NAME(id) = 'spt_values'
-- returns 6 rows = 6 columns

SELECT  *
FROM    dbo.spt_values
    -- 6 columns indeed

It's working (mysql) :

SELECT TABLE_NAME , count(COLUMN_NAME)
FROM information_schema.columns
GROUP BY TABLE_NAME 

SELECT TABLE_SCHEMA
    , TABLE_NAME
    , number = COUNT(*) 
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_SCHEMA, TABLE_NAME;

This one worked for me.

db2 'describe table "SCHEMA_NAME"."TBL_NAME"'

Since all answers are using COUNT(), you can also use MAX() to get the number of columns in a specific table as

SELECT MAX(ORDINAL_POSITION) NumberOfColumnsInTable
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'YourDatabaseNameHere'
      AND 
      TABLE_SCHEMA = 'YourSchemaNameHere'
      AND
      TABLE_NAME = 'YourTableNameHere';

See The INFORMATION_SCHEMA COLUMNS Table

    SELECT table_name, COUNT(*) AS column_count
    FROM information_schema.columns
    WHERE table_schema = 'your_schema_name'
    GROUP BY table_name

For list of all tables in your schema with their respective column counts. You may need to add table_catalog = 'your_database_name' condition in your where clause.

Here is how you can get a number of table columns using Python 3, sqlite3 and pragma statement:

con = sqlite3.connect(":memory:")    
con.execute("CREATE TABLE tablename (d1 VARCHAR, d2 VARCHAR)")
cur = con.cursor()
cur.execute("PRAGMA table_info(tablename)")
print(len(cur.fetchall()))

Source