Ways To Check Weather the Column Exist In The SQL Server Table Or Not

Chand Dakhara May 1, 2023 0 438

There are several ways to check if a column exists in a SQL Server table. Let’s start.

1. Using the “INFORMATION_SCHEMA.COLUMNS” system view:

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table_name' AND COLUMN_NAME = 'column_name')
BEGIN
   -- Column exists in the table
END

2. Using the “sys.columns” system catalog view:

IF EXISTS(SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('table_name') AND name = 'column_name')
BEGIN
   -- Column exists in the table
END

3. Using the “sysobjects” and “syscolumns” system tables:

IF EXISTS(SELECT * FROM sysobjects WHERE id = OBJECT_ID('table_name') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
   AND EXISTS(SELECT * FROM syscolumns WHERE id = OBJECT_ID('table_name') AND name = 'column_name')
BEGIN
   -- Column exists in the table
END

4. Using the “COLUMNPROPERTY” function:

IF COLUMNPROPERTY(OBJECT_ID('table_name'), 'column_name', 'ColumnId') IS NOT NULL
BEGIN
   -- Column exists in the table
END

All of the above methods check if a column with the specified name exists in a table with the specified name. You can use any of these methods in your SQL Server management tool or in your C# code by executing the query using a “SqlCommand” object.

Chand Dakhara

Web Developer