Introduction
Finding the number of columns in a database table can be done by querying the information schema (INFORMATION_SCHEMA.COLUMNS), using system catalog tables, or using database-specific commands like DESCRIBE (MySQL) or \d (PostgreSQL). In Python, pandas provides len(df.columns) or df.shape[1]. The approach depends on whether you are working with SQL databases, CSV files, or in-memory data structures.
The INFORMATION_SCHEMA is part of the SQL standard and works across MySQL, PostgreSQL, SQL Server, and other databases:
1-- Count columns in a specific table
2SELECT COUNT(*) AS column_count
3FROM INFORMATION_SCHEMA.COLUMNS
4WHERE TABLE_NAME = 'users'
5 AND TABLE_SCHEMA = 'my_database';
6
7-- List all columns with their types
8SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
9FROM INFORMATION_SCHEMA.COLUMNS
10WHERE TABLE_NAME = 'users'
11 AND TABLE_SCHEMA = 'my_database'
12ORDER BY ORDINAL_POSITION;
MySQL
1-- Count columns
2SELECT COUNT(*) AS column_count
3FROM INFORMATION_SCHEMA.COLUMNS
4WHERE TABLE_NAME = 'users'
5 AND TABLE_SCHEMA = DATABASE();
6
7-- DESCRIBE shows all columns
8DESCRIBE users;
9-- Or: SHOW COLUMNS FROM users;
10
11-- Quick column count from DESCRIBE
12SELECT COUNT(*)
13FROM INFORMATION_SCHEMA.COLUMNS
14WHERE TABLE_NAME = 'users'
15 AND TABLE_SCHEMA = DATABASE();
PostgreSQL
1-- Using INFORMATION_SCHEMA
2SELECT COUNT(*) AS column_count
3FROM INFORMATION_SCHEMA.COLUMNS
4WHERE TABLE_NAME = 'users'
5 AND TABLE_SCHEMA = 'public';
6
7-- Using PostgreSQL system catalog (faster)
8SELECT COUNT(*)
9FROM pg_attribute
10WHERE attrelid = 'users'::regclass
11 AND attnum > 0 -- Exclude system columns
12 AND NOT attisdropped; -- Exclude dropped columns
13
14-- List columns with \d in psql
15-- \d users
SQL Server
1-- Using INFORMATION_SCHEMA
2SELECT COUNT(*) AS column_count
3FROM INFORMATION_SCHEMA.COLUMNS
4WHERE TABLE_NAME = 'Users'
5 AND TABLE_SCHEMA = 'dbo';
6
7-- Using system catalog
8SELECT COUNT(*)
9FROM sys.columns
10WHERE object_id = OBJECT_ID('dbo.Users');
11
12-- sp_columns stored procedure
13EXEC sp_columns 'Users';
SQLite
1-- PRAGMA table_info returns one row per column
2SELECT COUNT(*) AS column_count
3FROM pragma_table_info('users');
4
5-- Or list all columns
6PRAGMA table_info('users');
7-- Returns: cid, name, type, notnull, dflt_value, pk
Oracle
1-- Using ALL_TAB_COLUMNS
2SELECT COUNT(*) AS column_count
3FROM ALL_TAB_COLUMNS
4WHERE TABLE_NAME = 'USERS'
5 AND OWNER = 'MY_SCHEMA';
6
7-- Or USER_TAB_COLUMNS for the current schema
8SELECT COUNT(*)
9FROM USER_TAB_COLUMNS
10WHERE TABLE_NAME = 'USERS';
11
12-- DESCRIBE command in SQL*Plus
13DESCRIBE USERS;
Python: pandas
1import pandas as pd
2
3# From CSV file
4df = pd.read_csv("data.csv")
5print(f"Columns: {len(df.columns)}") # Number of columns
6print(f"Shape: {df.shape}") # (rows, columns)
7print(f"Columns: {df.shape[1]}") # Just column count
8
9# List column names
10print(df.columns.tolist())
11
12# From database
13import sqlite3
14conn = sqlite3.connect("mydb.sqlite")
15df = pd.read_sql("SELECT * FROM users LIMIT 1", conn)
16print(f"Column count: {len(df.columns)}")
17print(f"Column names: {df.columns.tolist()}")
Python: Database Connectors
1import sqlite3
2
3conn = sqlite3.connect("mydb.sqlite")
4cursor = conn.cursor()
5
6# Execute and check cursor.description
7cursor.execute("SELECT * FROM users LIMIT 0")
8column_count = len(cursor.description)
9column_names = [desc[0] for desc in cursor.description]
10print(f"Columns: {column_count}")
11print(f"Names: {column_names}")
1# MySQL with mysql-connector-python
2import mysql.connector
3
4conn = mysql.connector.connect(host="localhost", database="mydb")
5cursor = conn.cursor()
6
7cursor.execute("""
8 SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
9 WHERE TABLE_NAME = 'users' AND TABLE_SCHEMA = 'mydb'
10""")
11count = cursor.fetchone()[0]
12print(f"Column count: {count}")
Python: CSV Without pandas
1import csv
2
3with open("data.csv", "r") as f:
4 reader = csv.reader(f)
5 header = next(reader)
6 print(f"Column count: {len(header)}")
7 print(f"Column names: {header}")
Dynamic Column Count in Queries
1-- Generate a query using column count (useful for dynamic SQL)
2-- MySQL: build a SELECT with all columns
3SELECT GROUP_CONCAT(COLUMN_NAME)
4FROM INFORMATION_SCHEMA.COLUMNS
5WHERE TABLE_NAME = 'users'
6 AND TABLE_SCHEMA = DATABASE();
7
8-- Check if a specific column exists
9SELECT COUNT(*) > 0 AS column_exists
10FROM INFORMATION_SCHEMA.COLUMNS
11WHERE TABLE_NAME = 'users'
12 AND TABLE_SCHEMA = DATABASE()
13 AND COLUMN_NAME = 'email';
Common Pitfalls
Not filtering by TABLE_SCHEMA: If multiple databases have tables with the same name, WHERE TABLE_NAME = 'users' without a schema filter returns columns from all of them, inflating the count. Always include TABLE_SCHEMA = DATABASE() (MySQL) or TABLE_SCHEMA = 'public' (PostgreSQL).
Case sensitivity in table names: PostgreSQL stores unquoted identifiers as lowercase. Oracle stores them as uppercase. WHERE TABLE_NAME = 'Users' fails if the table was created as users (PostgreSQL) or USERS (Oracle). Match the case used by your database.
Including dropped columns in PostgreSQL: The pg_attribute catalog includes columns that were ALTER TABLE DROP COLUMN'd (marked attisdropped = true). Always filter with AND NOT attisdropped to get the current column count.
Using SELECT * LIMIT 0 for column count: While SELECT * FROM table LIMIT 0 works to get column metadata via cursor.description, some databases still prepare the full query plan. For large tables with complex views, querying INFORMATION_SCHEMA is faster.
Confusing column count with row count: INFORMATION_SCHEMA.COLUMNS gives the number of columns (attributes). SELECT COUNT(*) FROM table gives the number of rows (records). These are fundamentally different dimensions of the table.
Summary
Use SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table' for a standard SQL approach
Use database-specific catalogs (pg_attribute, sys.columns, pragma_table_info) for better performance
In Python, use len(df.columns) or df.shape[1] with pandas
Use cursor.description after executing a query to get column metadata from any Python DB connector
Always filter by schema/database name to avoid counting columns from other schemas
Match table name case to your database's convention (lowercase for PostgreSQL, uppercase for Oracle)
