How to Check if a Column Exists in a Table in SQL

1. Introduction

Columns are one of the two main building blocks of tables. Generally, checking for a column’s existence within a table is a common practice in database management. This process enables users to perform conditional updates, avoid runtime errors in SQL scripts, and ensure integrity.

Moreover, each major database management system (DBMS) offers unique column check methods. This includes using a combination of queries and system information views.

In this tutorial, we’ll see how to check whether a column exists in a table.

Specifically, we’ll focus on three DBMSs with practical examples:

• MSSQL
• MySQL
• PostgreSQL

To that end, we’ll use our Simple University Schema.

2. Use Cases

There are multiple cases where checking a column’s existence may be useful:

• schema management: checking for column existence before altering a table ensures changes are applied correctly
• conditional updates: if a column exists, specific updates or calculations can be performed
• automated scripts: scripts often handle dynamic operations that require column presence checks to prevent query errors

Further, we see instructions and code examples for MSSQL, MySQL, and PostgreSQL.

3. Table Column Check in MSSQL

The information schema enables users to work with database metadata in MSSQL. To support this, the DBMS stores the database structure and many other aspects of the database information:

• • table names
  • table schema
  • table types

• others

The sqlcmd utility enables users to employ Transact-SQL statements in different ways:

• query editor
• system terminal

Knowing this, we can move on to checking for column existence.

3.1. Construct the Query

In MSSQL, checking for column existence is commonly achieved using the INFORMATION SCHEMA – COLUMNS view.

Let’s create an .sql file to setup the query:

$ cat CoulmnTest.sql
Use University;
if exists (select
               *
           from
               INFORMATION_SCHEMA.columns
           where
               table_name = 'Course'
               and column_name = 'textbook'
)
PRINT 'Column exists';
ELSE
PRINT 'Column does not exist';
GO

In the above example, the IF EXISTS statement examines the COLUMNS view. For example, if there is a column FirstName, it prints Column exists. Otherwise, it prints Column does not exist.

3.2. Run the Query

Let’s run the file from the system terminal:

$ sudo sqlcmd -S localhost -U sa -P '<ENTER_PASSWORD>' -No -i ColumnTest.sql
Changed database...
Column exists

In the above command, sqlcmd employs several options:

• -S: the target MSSQL server
• -U sa: system administrator account
• -P: password for the account
• -No: set the encryption option as optional
• -i: set the file containing Transact-SQL statements

The above code checks the column length of the given table name. However, this approach works if the user has permission to view the object. As a result, if the column length is NULL, we get the message Column does not exist. Otherwise, we see Column exists.

We can also try changing the column name to check whether the ELSE statement works as intended. This method is reliable for conditional scripting in MSSQL.

3.3. COL_LENGTH Function

Alternatively, we can also use the COL_LENGTH function.

Let’s make a small change to the .sql file:

$ cat ColumnTest2.sql
Use University;
IF COL_LENGTH('Course','book') IS NULL
    BEGIN
        PRINT 'Column does not exist';
    END
ELSE
    BEGIN
        PRINT 'Column exists';
    END
GO

Here, COL_LENGTH provides the length of a column.

Consequently, we get the same result on running the code:

$ sudo sqlcmd -S localhost -U sa -P '<ENTER_PASSWORD>' -No -i ColumnTest.sql 
Changed database context ...
Column does not exist

In the same way, we can check the case of the MySQL database.

4. Table Column Check in MySQL

Similar to  MSSQL, in MySQL, column existence checks are often performed using the INFORMATION SCHEMA database. As a matter of fact, this database stores metadata about all tables and columns within all the databases.

In this case, we use the COLUMNS table that contains data about columns in tables.

To start with, we log in to the MySQL server:

$ sudo mysql -u root -p

Let’s write a query to check whether a specified column exists in the Course table:

mysql> SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = 'University' AND table_name = 'Course' AND column_name = 'name';
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)

Further, we break down the components in the above example:

• table_schema specifies the database name
• table_name specifies the table name
• column_name is the column being checked

As a result, if the output is greater than zero, the column exists. Otherwise, it doesn’t.

5. Table Column Check in PostgreSQL

In PostgreSQL, checking for a column’s existence can be done using the INFORMATION SCHEMA COLUMNS table. In essence, it contains metadata about all columns in the database.

5.1. Logging into Database

Same as before, we connect to the PostgreSQL server with the default user postgres:

$ sudo --login --user=postgres

Then, in the new prompt, we run psql:

postgres@ubuntu:~$ psql
psql (14.13 (Ubuntu ...)
Type "help" for help.
postgres=#

Likewise, we connect to the database:

postgres=# \c university
You are now connected to database ...
sampledb=#

Finally, we’re logged into university.

5.2. Column Existence Check

Essentially, a fairly basic PostgreSQL query checks for the existence of a column:

university=# SELECT column_name FROM information_schema.columns WHERE table_name = 'course' AND column_name = 'textbook';
column_name 
-------------
textbook
(1 row)

As a result, if the column exists, the query returns the column name. Otherwise, we get no results.

6. Conclusion

In this article, we saw how to check whether a column exists within a table. Overall, we worked with three different DBMSs:

• MSSQL
• MySQL
• PostgreSQL

Each system has unique methods for performing the checks. Generally, these methods make use of metadata queries. Such checks help ensure database scripts run smoothly. As a result, we can prevent unexpected errors, and enable conditional updates based on schema configurations.