Check If the Column Exists in SQL

In SQL, columns are the building blocks of any relational database or table, to be more precise. A column in SQL represents a type of entity for the data that is stored in it. It contains various properties about the data such as the data type, the format that the data follows, and the constraints or rules that the data inserted in that column should follow.

However, what you may not think as a big deal is trying to find out if a column exists within the table. Although this may seem less common, there is nothing as infuriating as attempting to select or insert the values into a column that does not exist.

It is therefore good, especially if you are dealing with an extremely large and new table, to verify if a given column exists in the table.

That is exactly what we will cover in this post. We will explore all the methods that you can use to check whether a given column exists within a database table.

SQL Columns

Let us go back to SQL fundamentals and talk about what is a column and what it entails.

In SQL, a column is a vertical structure within a database table that stores a specific type of data. A column is comprised of various characteristics or, more appropriately, properties. These define what that column is and how it differs from the rest.

They include the following:

Name – Each column has a unique name within a table which is used to reference it in SQL queries.
Data Type – In SQL, each column has a defined data type that specifies the type of data it can hold (e.g., INTEGER, VARCHAR, DATE).
Constraints – You can think of column constraints as some of the rules that govern how the data in it is stored. For example, some common constraint includes NOT NULL which prevents you from storing the empty or NULL values.
Default Value – A column can have a default value assigned to it. This ensures that if you do not provide a value when inserting a row, the default value works as the placeholder instead of adding a NULL value. This is awesome when dealing with external data sources and imports.

With the basics of columns out of the way, let us proceed and learn how to verify their existence in a given table.

Method 1: Information Schema

If you used a MySQL database, you are probably familiar with the “information_schema” database or system catalog to be more precise.

This is a database of databases. It contains all the metadata information about the objects in the server.

One of the ways that we can use to check if a column exists in a table is querying the information schema as shown in the following query syntax:

SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = table_name AND COLUMN_NAME = 'column_name';

This should return the information on the selected columns if the table name and the matching column name is found.

Method 2: Describe Statements

In other databases, MySQL and PostgreSQL for example, they support the SHOW COLUMNS and DESCRIBE statements which allows us to display the information about the table columns.

The syntax is as follows:

SHOW COLUMNS FROM table_name LIKE 'column_name';

This should show the column that matches the specified name.

Method 3: Sys.columns

When it comes to SQL Server, we can check if a column exists in a table by querying the “sys.columns” catalog view. This is like the “information_schema” of the SQL Server.

The query syntax is as follows:

SELECT name

FROM sys.columns

WHERE object_id = OBJECT_ID('table_name') AND name = 'column_name';

Conclusion

In this post, we explored the various techniques that we can use to check if a column exists within a given database table.