SQL Get Column Names

Structured Query Language or SQL is the foundational language for interacting with relational databases. It is a simple and powerful language that makes the interaction with relational databases much more manageable and enjoyable.

Although major database engines adopt Standard SQL, the implementation may vary slightly when performing specific queries.

One such operation is fetching the column names within a given table.

This tutorial will provide the queries for fetching the column names of a given table in major database engines such as MySQL, PostgreSQL, Oracle, and Microsoft SQL Server.

Setting Up Sample Database

Let us set up a simple database for illustration purposes. If you already have a database on which to work, please skip this section.

Otherwise, copy the code for your database engine as shown below:

-- universal
CREATE DATABASE sample_db;

-- switch database
USE sample_db; -- mysql
\c sample_db; -- PostgreSQL
CONNECT system/password@sample_db; -- Oracle DB
USE sample_db; -- SQL Server

-- create table
CREATE TABLE sample_table(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
full_name CHAR(50) NOT NULL,
country CHAR(10)
);
CREATE TABLE sample_table(
id serial PRIMARY KEY,
full_name VARCHAR(50) NOT NULL,
country VARCHAR(10)
);
CREATE TABLE sample_db.sample_table(
id NUMBER generated BY DEFAULT AS IDENTITY,
full_name VARCHAR(50) NOT NULL,
country VARCHAR(10),
PRIMARY KEY(id)
);
CREATE TABLE sample_table(
id INT PRIMARY KEY IDENTITY(1,1),
full_name VARCHAR(50) NOT NULL,
country VARCHAR(10)
);

-- insert data
INSERT INTO sample_table(full_name, country) VALUES
('Alice Downey', 'US'),
('Michael Tweer', 'IR'),
('Peter Green', 'CA'),
('Bilha Ann', 'KE'),
('Sooty R', 'MO')

The above queries allow you to create a sample database, a sample database with a simple schema, and it allow you to insert sample data.

NOTE that the create table statements are in the order of MySQL, PostgreSQL, Oracle, and SQL Server. Choose and use the one that fits your database engine.

List all Columns in a Specified Table – MySQL

Once we have a sample database setup, let us discuss how to fetch the column names.

In MySQL, you can get the names of all columns in a given table using the show command.

The syntax for such query is as shown:

SHOW COLUMNS FROM database_name.table_name;

For example, to get the column names from the sample_table:

SHOW COLUMNS FROM sample_db.sample_table;

The query above should return the information about the columns in the table as shown:

You can also use the information schema to fetch the column names of a given table. The syntax is as shown:

SELECT * FROM information_schema.column WHERE table_schema = 'database_name';

For example, to get the table name and column name of the sample_table, run:

SELECT TABLE_NAME, column_name FROM information_schema.columns WHERE table_schema = 'sample_db';

The above query should return:

Another quick and simple method to fetch the column names of a table is to use the describe query. The syntax is as shown:

DESCRIBE TABLE_NAME;

For example:

DESCRIBE sample_table;

List all Column in a Target Table – PostgreSQL

Let us now switch to PostgreSQL. To get the names of all columns in a given table, use the information schema DB as:

SELECT * FROM information_schema.columns WHERE table_schema = 'database_name' AND TABLE_NAME = 'table_name';

For example:

SELECT table_schema, TABLE_NAME, column_name FROM information_schema.columns WHERE table_schema = 'sample_db' AND TABLE_NAME = 'sample_table';

The query above should return the schema name, table name, and column name from the sample _table.

List All Columns in a Table – Oracle

In the Oracle database, you can fetch the column names of a given table by running:

SELECT column_name FROM user_tab_cols WHERE TABLE_NAME = 'table_name';

For example, in our sample_table, we can do:

SELECT column_name FROM user_tab_cols WHERE TABLE_NAME = 'sample_table';

List All Columns in a Table – SQL Server

In SQL Server, you can use the query provided below to get the list of columns in a given table:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_name';

You can also use the sp_columns command as:

EXEC sp_columns 'table_name'

The query should list all the column names in the specified table.

Closing

Throughout this tutorial, you discovered various methods of fetching column names of a given table in all the popular database engines.