How to Retrieve Column Names From a Table in SQL

1. Introduction

Retrieving column names from a table in SQL is a common task that database administrators and developers often perform. Furthermore, this process can be useful for dynamic SQL operations.

In this tutorial, we’ll explore several methods to retrieve column names in SQL. To begin with, we’ll cover the SHOW COLUMNS command, INFORMATION_SCHEMA, and the DESCRIBE statement in MySQL. After that, we’ll discuss INFORMATION_SCHEMA.COLUMNS, followed by the SYS.COLUMNS and SYS.TABLES views. Lastly, we’ll cover the INFORMATION_SCHEMA and the\d command in PostgreSQL.

Notably, we execute the queries on MySQL, PostgreSQL, and MSSQL server database management systems using tables in the Baeldung University database schema.

2. Retrieve Column Names in MySQL

In this section, we’ll explore approaches to retrieve column names in a table in MySQL.

2.1. Using SHOW COLUMNS Command

The first approach to viewing column names in MySQL is SHOW COLUMNS. Specifically, the SHOW COLUMNS command provides detailed information about the columns in a table:

• field name
• type
• nullability
• key information
• default value
• extra information

For example, to view columns from the Course table, we use SHOW COLUMNS followed by the table name:

SHOW COLUMNS Course
Field	Type	Null	Key	Default	Extra   
id	varchar(10)	NO	PRI		
name	varchar(60)	YES			
textbook	varchar(100)	YES		
credits	int	YES			        
is_active	varchar(10)	YES		
department_id	int	YES	MUL		

This output helps us in understanding the structure of the Course table in detail.

2.2. Using INFORMATION_SCHEMA

Another approach to viewing column names is using the INFORMATION_SCHEMA. Specifically, the INFORMATION_SCHEMA.COLUMNS table contains metadata about columns in all tables. This query retrieves the column names for a specific table within a specified database.

For instance, we print the column names from the Course table using INFORMATION_SCHEMA:

SELECT COLUMN_NAME 
FROM information_schema.columns
WHERE TABLE_NAME = 'course'; 
COLUMN_NAME
credits
department_id
id
is_active
name
textbook

Thus, this output lists all column names of the Course table in the University schema.

2.3. Using the DESCRIBE Statement

One more approach to printing column names from a table is the DESCRIBE statement. Similarly, the DESCRIBE statement provides a quick overview of the columns in a table.

For example, let’s see what using this statement on the Course table produces:

DESCRIBE course;
Field           Types           Null    Key
id	        varchar(10)	NO	PRI
name	        varchar(60)	YES	   
textbook	varchar(100)	YES	   
credits	        int	        YES	   
is_active	varchar(10)	YES	   
department_id	int	        YES	MUL

Such a summary provides an overview of the column details for the Course table.

3. Retrieve Column Names in MSSQL Server

In this section, we explore methods for retrieving column names from a table in SQL Server.

3.1. Using the INFORMATION_SCHEMA View

SQL Server provides a built-in information schema view called INFORMATION_SCHEMA that contains metadata about all columns in the database. Specifically, we can use this to fetch column names in SQL Server:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Course'
ORDER BY ORDINAL_POSITION;

As a result, the query returns the column names:

COLUMN_NAME
id
name
textbook
credits
is_active
department_id

In the above example, we use ORDINAL_POSITION to order the columns by their position within the table. In general, this method is straightforward and widely used due to its simplicity and reliability on standard SQL.

3.2. Using the SYS.COLUMNS and SYS.TABLES Views

For a more detailed exploration, we can use the SYS.COLUMNS and SYS.TABLES system catalog views. These views provide more comprehensive metadata about tables and columns.

For instance, to view the comprehensive metadata about the Course table, we perform a query using INNER JOIN:

SELECT c.name AS ColumnName
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'Course'
ORDER BY c.column_id;

Moreover, let’s see an explanation of the key elements:

• SYS.COLUMNS contains a row for each column of an object that has columns, such as tables and views
• SYS.TABLES contains a row for each table object, providing table-specific information
• c.name is the name of the column
• c.column_id orders the columns by their position within the table

As a result, the query produces the column names as its output:

ColumnName
----------
id
name
textbook
credits
is_active
department_id

The command retrieves the column names of the Course table in SQL Server by joining the SYS.COLUMNS and SYS.TABLES system catalog views:

• SELECT c.name AS ColumnName selects the name column from the SYS.COLUMNS view and renames it to ColumnName in the result set
• FROM SYS.COLUMNS c specifies the SYS.COLUMNS view as the source table and assigns it an alias c
• INNER JOIN SYS.TABLES t ON c.object_id = t.object_id joins the SYS.TABLES view with the SYS.COLUMNS view on the object_id column, ensuring that only columns belonging to the tables in SYS.TABLES are considered
• The object_id uniquely identifies each table and column in the database
• WHERE t.name = ‘Course’ filters the results to include only the columns of the table named Course
• ORDER BY c.column_id orders the result set by the column_id

Consequently, this method is preferred when we need more control and additional details about the columns and tables.

4. Retrieve Column Names in PostgreSQL

In this section, we check out various methods for retrieving column names from a table in PostgreSQL.

4.1. Using the INFORMATION_SCHEMA View

To begin with, the INFORMATION_SCHEMA view provides metadata about the columns of all tables. By querying this view, we can retrieve the column names for a specific table. Let’s take a look at the result for the Course table:

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'Course';
column_name
------------
id
name
textbook
credits
is_active
department_id

In this query, the column names of the Course table are listed, providing a fairly simple method to access this information using standard SQL.

4.2. Using the \d Command

Additionally, in the psql terminal, the \d command provides a detailed table description that includes its column names. Consequently, by executing this command, we can quickly gain a comprehensive overview of the table structure in PostgreSQL.

Let’s examine the result when we execute this command for the Course table:

\d Course
Table "public.Course"
    Column     |            Type             | Collation | Nullable | Default
---------------+-----------------------------+-----------+----------+---------
id             | integer                     |           | not null |
name           | character varying           |           |          |
textbook       | character varying           |           |          |
credits        | integer                     |           |          |
is_active      | boolean                     |           |          |
department_id  | integer                     |           |          |
Indexes:
    "course_pkey" PRIMARY KEY, btree (id)

In this example, the \d command returns comprehensive metadata about the Course table, including the column names, types, collation, nullability, and default values.

5. Conclusion

In this article, we covered various methods to retrieve column names from a table in SQL.

Initially, we discussed the approaches to retrieve the column names in MySQL. Subsequently, we explored the options in SQL Server. Lastly, we covered the commands in PostgreSQL.

Moreover, retrieving column names from a table in SQL can be accomplished through various methods in MySQL, SQL Server, and PostgreSQL. By understanding and utilizing these methods, we can efficiently manage and interact with custom data schemas, ensuring effective database operations and development practices.