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

Tutorialsrack 23/10/2021 SQL Server

In this article, you will learn how to check if a column exists in a table or not in the SQL server. There are various in-built system catalog views or metadata functions that you can use to check the existence of columns in a table of SQL Server. There are various ways to check whether a column exists in a table or not. Here are some examples to achieve this.

Example 1: Check Existence of a Column Using sys.columns

In this example, we used the Sys.columns. It is another catalog view in SQL Server which returns a row for each column of an object that has columns, such as views or tables. See the below example:

IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'columnName' AND Object_ID = Object_ID(N'schemaName.tableName')) 
BEGIN 
-- Column Exists 
PRINT 'Column Exists'
END
ELSE
BEGIN
PRINT 'Column Not Exists'
END

Example 2: Check Existence of a Column Using COL_LENGTH() Function

In this example, we used the COL_LENGTH() function. This function is a SQL Server Metadata function, which returns the defined length of a column, in bytes. See the below example:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN 
-- Column Exists 
PRINT 'Column Exists'
END
ELSE
BEGIN
PRINT 'Column Not Exists'
END

Example 3: Check Existence of a Column Using INFORMATION_SCHEMA.COLUMNS

In this example, we used the INFORMATION_SCHEMA.COLUMNS.The INFORMATION_SCHEMA.COLUMNS view allows you to get information about all columns for all tables and views within a database. See the below example: 

IF EXISTS 
(
  SELECT * 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE table_name = N'schemaName.tableName'
  AND column_name = N'columnName'
)
BEGIN 
-- Column Exists 
PRINT 'Column Exists'
END
ELSE
BEGIN
PRINT 'Column Not Exists'
END

Example 4: Check Existence of a Column Using sysobjects, syscolumns

In this example, we used the sysobjects & syscolumns. The sysobjects contain one row for each object that is created within a database, such as a constraint, default, log, rule, and stored procedure. And the syscolumns returns one row for every column in every table and view, and a row for each parameter in a stored procedure in the database.  See the below example:

IF EXISTS (
SELECT * 
FROM 
     sysobjects, syscolumns 
WHERE 
     sysobjects.id = syscolumns.id 
     and sysobjects.name = 'schemaName.tableName' 
     and syscolumns.name = 'columnName')
BEGIN 
-- Column Exists 
PRINT 'Column Exists'
END
ELSE
BEGIN
PRINT 'Column Not Exists'
END

I hope this article will help you to understand how to check if a column exists in a table or not in the SQL Server.

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!

Related Posts