Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

community creator
sql

How to check if a column exists in a table

Behzad Ahmad

Overview

In SQL, the COL_LENGTH() function is used to check the existence of the column in the database.

Syntax

COL_LENGTH ( 'tableName' , 'columnName' )

Parameters

This function takes the following two parameters:

  • tableName: The name of the table that contains our desired column.
  • columnName: The name of the column for which we want the length.

Return value

This function returns the length of the desired column.

Example

Let’s create an Employee table for our test database.

CREATE TABLE Employee(
  Name varchar(40),
  Field varchar(30),
  Id int
);
Creating a table

Now that we have created our database and table, let’s insert some data in the Employee table. We’ll use the following query to insert the data:

INSERT INTO Employee
VALUES ('Behzad','Edpresso',1),
       ('Dian','Content',2),
       ('Arsal','Manager',3),
       ('Soha','HR',4);
Inserting data into Employee table

To check whether the data has been inserted successfully, we’ll run the following query:

SELECT * FROM Employee
Retrieving data from the table

In order to check the existence of the column in the table, we run the following query:

IF COL_LENGTH('Employee','Id') IS NOT NULL
    PRINT 'Column Exists';
ELSE
    PRINT 'Column Does Not Exist';
Using COL_LENGTH() to check the existence of column

Explanation

  • Line 1: The COL_LENGTH() function returns the length of the column. We set a condition that checks for a NOT NULL value for the column length.

  • Lines 2–4: If the length is NOT NULL, we print Column Exists. Otherwise, we print Column Does Not Exist.

RELATED TAGS

community creator
sql
RELATED COURSES

View all Courses

Keep Exploring