How To Check If A Column Exists In SQL Server Table

In this blog, you will learn about check if column Exists or not in SQL Server Table.

In this blog, we are going to see how we can add two columns to an existing table. While adding the columns, we need to check whether the columns are already available or not. I hope you all know how to add new column to an existing table. However, let me go ahead and explain the steps and various approaches to check if the column already exists.

For demo purposes, I have used the below Apps and created a new database called “Tutorial”

1. MS SQL Management Studio version 18.5.1
2. MS SQL Server 2019 Developer Edition (64 bit)

To begin with, let us create a new table. The script for creating the table below

CREATE TABLE dbo.Employee
(
    ID INT IDENTITY(1,1),
    FirstName VARCHAR(50)
)

Using this script, we can create a table called “Employee” with two columns “ID” and “FirstName”.

Now let us insert one or two records into this table using the below insert script.

INSERT INTO Employee(FirstName)
SELECT 'Peter' FirstName UNION ALL
SELECT 'Robert' FirstName UNION ALL
SELECT 'Michael'  FirstName

So far so good. Right. Now we are going to see the multiple ways to check whether the column exists in a table.

Assume that we are going to add a new column “LastName” to the table “Employee”

Here we are going to discuss three approaches through which we can check if a column exists in a table.

Using COL_LENGTH

This function returns the defined length of a column, in bytes.

The below script can use used for checking “LastName” in Employee table

IF COL_LENGTH('dbo.Employee', 'LastName') IS NOT NULL
PRINT 'Column- LastName Exists'
ELSE
PRINT 'Column- LastName doesn''t Exists'

Please refer to the Microsoft documentation for more details on COL_LENGTH

Using sys.columns

The below script can be used to check whether the column exists in a table. In our example, we are using “LastName” as column and “Employee” as table

IF EXISTS(SELECT 1 FROM sys.columns
WHERE Name = N'LastName'
AND Object_ID = Object_ID(N'dbo.Employee'))
PRINT 'Column- LastName Exists'
ELSE
PRINT 'Column- LastName doesn''t Exists'

For more information on sys.columns, please visit Microsoft documentation.

Using Information_Schema

Please use the below script for checking the column exists in a table

IF EXISTS
(
    SELECT *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'Employee'
    AND column_name = 'LastName'
)
PRINT 'Column- LastName Exists'
ELSE
    PRINT 'Column- LastName doesn''t Exists'

For more information on INFORMATION_SCHEMA.COLUMNS , please refer to Microsoft documentation.

My personal preference would be option one. I hope this may help when you are handling similar use cases. Thank you for reading my blog. Please leave your comments in the comment box below.