SQL Server Check If Column Exists

by

In this SQL Server tutorial, you will learn about SQL Server and check if the column Exists.

As a database developer, you must know how to check if a column exists in the table, so I have explained several approaches to checking column existence here.

Let’s start,

SQL Server Check If Column Exists

You can use multiple methods to check if a column exists in SQL Server. I will explain each of the two most commonly used methods step by step. Let’s start.

SQL Server Check If Column Exists using COL_LENGTH

The COL_LENGTH() function returns the column length in bytes;

The syntax of the COL_LENGTH() function is given below.

COL_LENGTH(table_name, column_name)

Where COL_LENGTH() function takes two parameters:

  • table_name: It is the name of the table that contains the column.
  • column_name: It is the name of the column whose length you want to find in bytes. Remember, if a column doesn’t exist, it returns the NULL.

For example, to check the existence of a column, you can use the CO_LENGTH() as shown in the query below.

IF COL_LENGTH('Products', 'product_id') IS NOT NULL
PRINT 'Specified product_id column exist'
ELSE
PRINT 'Specified product_id column doesn''t exist'
SQL Server Check If Column Exists using COL_LENGTH

The output shows that the specified product_id column exists in the Products table. Let’s understand the query part one by one.

This query part IF COL_LENGTH(‘Products’, ‘product_id’) IS NOT NULL means:

  • If the product_id column exists in the Products table, COL_LENGTH returns its size in bytes. Since the size is not null, the IF condition is true and prints “Specified product_id column exist”.
  • If the product_id column does not exist in the Products table, COL_LENGTH returns NULL. In this case, the IF condition is false and prints “Specified product_id column doesn’t exist”.

This is how SQL Server checks if a Column Exists in a table or not using the COL_LENGTH() function.

SQL Server Check If Column Exists using INFORMATION_SCHEMA.COLUMNS

The SQL server has INFORMATION_SCHEMA.COLUMNS view that you can query to check the existence of columns in a table. This view contains all the information about all the columns in the database.

Use the INFORMATION_SCHEMA.COLUMNS view to check again whether the product_id column exists in the Products table, as shown in the query below.

IF EXISTS (
    SELECT 1
    FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
    WHERE t.name = 'Products'
    AND c.name = 'product_id'
)
PRINT 'product_id column exist'
ELSE
PRINT 'product_id column doesn''t exist'
SQL Server Check If Column Exists using INFORMATION_SCHEMA.COLUMNS

The output shows that the product_id column exists in Product tables. Let me explain the query parts to you.

The above SQL query checks whether a column named product_id exists in a table named Products in a SQL Server database. It does this by using an IF EXISTS statement, which evaluates to true if the subquery inside it returns any results.

The subquery selects a value (1) from a joined view of sys.columns and sys.tables.

  • sys.columns is a system view containing information about columns in the database.
  • sys.tables is a system view containing information about tables in the database.

These two are joined on their object_id field, a unique identifier for database objects.

Next, the subquery filters records where the table’s name is ‘Products’ and the column’s name is ‘product_id’.

  • If the subquery finds at least one row matching these conditions (meaning the product_id column exists in the Products table), IF EXISTS evaluates to true, the query prints ‘product_id column exists’.
  • If no matching row is found (meaning the product_id column does not exist in the Products table), IF EXISTS evaluates to false, the query prints ‘product_id column doesn’t exist’.

From the above explanation, you understand how SQL Server checks the If column exists using INFORMATION_SCHEMA.COLUMNS.

Conclusion

In this SQL Server tutorial, you learned about SQL Server and how to check if a column exists using the COL_LENGTH() function and INFORMATION_SCHEMA.COLUMNS.

You may like to read: