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

There are two simple methods in SQL Server to check whether a given Column Exists in a Table or not. This is important when you are trying to add a new column in a table dynamically, without knowing that the column already exists, using a query in an application or through the SQL Server Management Studio.

1) Using sys.columns

In the first method, I am using the sys.columns object. It’s an in-build object in SQL Server, which returns a row for each column of an object (such as a Table or a View) that has columns.

I have an EmployeeDetails table, in which I want to check if column Area exists or not. The script will be,

SELECT 1 FROM sys.columns
    WHERE Name = N'Area'
    AND Object_ID = Object_ID(N'dbo.EmployeeDetails')

The output of the above script will return 1 if the column 'Area' exists in the table. Else, it will return nothing. You can use some other value (a string value) instead of 1 in the SELECT query.

I can use the above method in a more dynamic situation, such as, providing the table name dynamically to the Object_ID() method. For example,

SELECT T1.Spec_Table FROM Tab1 T1
    WHERE EXISTS(
        SELECT 1 FROM sys.columns
            WHERE Name = N'Occasion'
            AND Object_ID = Object_ID(N'dbo.' + T1.Spec_Table)
    )

The table Tab1 in the above script has a column name Spec_Table that has the name of many other tables, for example. I want only that tables which has the column Occasion. So here, I am passing the table name dynamically to the Object_ID() method.

Using COL_LENGTH() Function

In the 2nd method, I am using a built-in function called COL_LENGTH() to check if a particular column exists in the table. The method returns a defined length of a column, in bytes.

Again, in this example I am using the EmployeeDetails table.

IF COL_LENGTH('EmployeeDetails', 'PresentAddress') IS NOT NULL
    PRINT 'Column Exists'
ELSE
    PRINT 'Column doesn''t Exists'

The function COL_LENGTH() takes two arguments. The first argument is the table name and the second is the column name that you want to check. You put it in an IF…ELSE condition.

You can use above function with a SELECT query to check the length of a particular column in a table.

SELECT COL_LENGTH('EmployeeDetails', 'Area')

If the column Area exists in the table, the result will be a number (a figure) or else it will return a NULL.

Well, that’s it. Using the above methods, now you can avoid adding duplicate column names. Both are useful in different scenarios.

← PreviousNext →