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

In this article, we will look at how to check if a particular column exists in a database table or not. For checking the existence of a column we need to create the table first. So, let us create a table with some columns and data.

Creating table:

Syntax:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....);

Using the above syntax we will be creating a Student table. The student table will have three fields Name, Department, and Roll Number of a student. To create the table use the below query.

Query:

CREATE TABLE Student(Name varchar(40),
Department varchar(30),Roll_No int, );

Output:

This query will show the output as Commands completed successfully. It means that a student table is created in our database. To insert values in the table we have to use the INSERT query.

Insert  Data into the Table:

Syntax:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Using the above syntax we will be adding student data to our table. We have to insert the values according to the columns created. Use the below query to insert the data.

Query:

INSERT INTO Student
VALUES ('Rahul Sharma','Electronics',15),
       ('Soha Shaikh','Computer Science',24),
       ('Vivek Rao','Arts',31),
       ('Sonali Rane','Electronics',20);

Output:

We have added the data to our table. We can verify or print the data in the table using the SELECT query as below.

View Table Data:

Query:

SELECT * FROM Student

Output:

Checking Existence of the Column:

For checking the existence we need to use the COL_LENGTH() function.

Syntax:

COL_LENGTH ( 'table' , 'column' )

• COL_LENGTH() function returns the defined length of a column in bytes.
• We have to pass two parameters - table name and column name
• This function can be used with the IF ELSE condition to check if the column exists or not.

Now we use the below query to check the existence of a column.

Query:

IF COL_LENGTH('table_name','column_name') IS NOT NULL
    PRINT 'Column Exists';
ELSE
    PRINT 'Column does not Exists';

The above Student table has three columns Name, Department, and Roll Number. The below examples show how to check if a column exists in a database table.

Output 1:

Output 2:

Using COL_LENGTH() function we can find out if a column exists in our database table or not.