Practice Now
SQL
database
table structure
column count
data management

Find the number of columns in a table

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Databases

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.

SQL: INFORMATION_SCHEMA (Standard)

The INFORMATION_SCHEMA is part of the SQL standard and works across MySQL, PostgreSQL, SQL Server, and other databases:

sql
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

sql
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

sql
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

sql
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

sql
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

sql
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

python
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

python
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}")
python
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

python
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

sql
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)
Related reading
Course
Beginner
27 lessons
10 hours
System Design Fundamentals

Build a strong foundation in designing scalable, reliable distributed systems.

View the course
Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Codemia
© 2026 Codemia
Resources
Legal
Social
All Rights Reserved.