SQL SERVER – Checking If a Column Exists in a Table

One of the common tasks that developers deal with when working on database-related projects is ensuring that certain columns exist in a table. This task is particularly relevant when you’re working on scripts to modify the structure of a table, such as adding new columns, and you want to avoid errors caused by attempting to add an existing column. Let us see a script that will help check if a Column Exists in a Table.

Using INFORMATION_SCHEMA.COLUMNS

The INFORMATION_SCHEMA.COLUMNS view can be used to verify the existence of a column. The INFORMATION_SCHEMA views provide access to database metadata, including information about columns in all tables in the database.

IF EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'myTableName'
AND COLUMN_NAME = 'myColumnName')
BEGIN
-- Column Exists
END

In this example, a SELECT query is constructed to find a row in INFORMATION_SCHEMA.COLUMNS that match the specified table and column name. If such a row exists, the column exists in the table.

Using sys.columns

Another approach is to use the sys.columns view, which also provides metadata about columns in tables.

IF EXISTS(SELECT 1 FROM sys.columns
WHERE Name = N'columnName'
AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
-- Column Exists
END

In this case, the Object_ID function is used to get the ID of the table, and this is compared with the object_id column in sys.columns.

Using COL_LENGTH

You can also use the COL_LENGTH function, which returns the length of a specified column. If the column does not exist, COL_LENGTH returns NULL.

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
-- Column Exists
END

One advantage of this approach is that COL_LENGTH only returns data about committed changes, irrespective of the isolation level.

Wrapping Up

Each method has its advantages, and the best one to use depends on your specific circumstances. The INFORMATION_SCHEMA.COLUMNS and sys.columns views provide a wealth of information about columns, while the COL_LENGTH function can be faster since it uses cached database metadata.