Check if Column Exists in SQL Table or Not

Check if Column Exists in SQL Table or Not

Sometimes we may need to check whether a particular column exists in a table [or in database, in some scenarios] or not. We can always open table structure & check it manually. But in real programming, we need to check it with the help of SQL queries. We can verify same [Whether a column exists in SQL Server table] by executing SQL queries or calling a function .
This article will help you to find or check that column exists or not in 10 distinct ways.

1.
We can use ColumnProperty function to check whether column (Amount) exists for a given table name (i.e. Item). The OBJECT_ID function will return ID of the table. ColumnProperty method will then take Object_Id, Column_Name to search & ColumnId as parameter to see if the column exists or not.

--Checks in Item table for Amount column
USE Northwind;
IF COLUMNPROPERTY(OBJECT_ID('Item'), 'Amount', 'ColumnId') IS NULL
  PRINT 'does not exist'
ELSE
  PRINT 'exists'

2.
Our intention of finding a column in a table can also be attained with the help of SYSOBJECTS & SYSCOLUMNS with InnerJoin. We will check the Table Name in the SYSOBJECTS & Column Name in SYSCOLUMNS and join the query with ID column of both the tables.

--Checks in Orders table for OrderID column
USE Northwind;
IF((SELECT count(*)  
FROM SYSOBJECTS   
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID = SYSCOLUMNS.ID  
WHERE  
 SYSOBJECTS.NAME = 'Orders'  
 AND SYSCOLUMNS.NAME = 'OrderID')>0)
PRINT 'Exists'
ELSE
PRINT 'Does not exist'

3.
The another way is to use EXISTS Method to find a column name in a given table from INFORMATIONSCHEMA.Columns Table.

--Checks in Employees table for ReportsTo column
USE Northwind;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns 
           WHERE TABLE_NAME = 'Employees'
           AND COLUMN_NAME = 'ReportsTo')
PRINT 'Exists'
ELSE
PRINT 'Does not exist'

4.
We can also achieve the same with the help of COL_LENGTH method. This method will take 2 parameters viz. TableName and other ColumnName.

--Checks in Employees table for BirthDate column
USE Northwind;
IF ((COL_LENGTH('Employees','BirthDate'))>0)
PRINT 'Exists'
ELSE
PRINT 'Does not exist'

5.
If the count of Columns is greater than 0, then the column exits otherwise doesn’t exist. This logic can also be used in finding column in InformationSchema.Column Table.

--Checks in Employees table for Address column
USE Northwind;
if((SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'Employees' AND
 COLUMNS.Column_Name='Address')>0)
PRINT 'Exists'
ELSE
PRINT 'Does not exist'

6.
Using SYSCOLUMNS table, we can use its "name" column for columnname & match the ID with the OBJECT_ID method. OBJECT_ID method takes table name as parameter and returns Table ID. And then this ID can be used to match with SYSCOLUMNS’s ID.

--Checks in Employees table for Region column
USE Northwind;
IF((SELECT  COUNT(name)
            FROM    syscolumns
            WHERE   id = OBJECT_ID('Employees')
                    AND name = 'Region' )>0)
PRINT 'Exists'
ELSE
PRINT 'Does not exist'

7.
We can use User Defined Functions to serve our purpose. Example is given below:an UDF named CheckColumnExistsInTable is created with 2 parameters viz. TableName & Columname.This function will return a VARCHAR string Exists or Does Not Exists. Inside function, an IF condition is being used to find column name in SYSColumns Table.

--Checks in Employees table for City column
CREATE FUNCTION CheckColumnExistsInTable(@tablename VARCHAR(30) ,
@columnname VARCHAR(30))
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @message VARCHAR(20)
IF((SELECT COUNT(NAME) 
    FROM syscolumns 
    WHERE id=object_id('[' + @tablename + ']') AND NAME=@columnname)>0)
SET @message='Exists';
ELSE
SET @message='Does not Exists';
RETURN @message;
END


USE Northwind;
--Calling UDF
PRINT dbo.CheckColumnExistsInTable('Employees','City')

8.
COALESCE is the other option to check for a column in a table. With the below query, COALESCE will return COL_LENGTH if column exists, otherwise return 0.

--Checks in Employees table for FirstName column
USE Northwind;
IF((SELECT COALESCE(COL_LENGTH('Employees','FirstName'),0))>0)
PRINT 'Exists'
ELSE
PRINT 'Does not exist'

9.
Achieving the same with the help of Sub-Query can be possible.

--Checks in Employees table for TitleOfCourtesy column
USE Northwind;
IF((SELECT COUNT(Column_NAME) 
    FROM INFORMATION_SCHEMA.Columns c 
    WHERE c.Column_Name='TitleOfCourtesy' AND c.Table_Name=(SELECT Table_Name 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE Table_Name='Employees'))>0)
PRINT 'Exists'
ELSE
PRINT 'Does not exist'

10.
We will fetch the count of required Column_name i.e. FirstName (which should be equal to 1 if column exists) from INFORMATION_SCHEMA.COLUMNS table and match the Table_Name column of INFORMATION_SCHEMA.COLUMNS table with Table_Name column of INFORMATION_SCHEMA.TABLES table. It will return 1 if column exists in the table or else return 0. So, we will print messages if the column exits or does not exist.

--Checks in whole Northwing DB for column Firstname
USE Northwind;
IF((SELECT COUNT(COLUMNS.Column_Name) AS ColumnExists
    FROM INFORMATION_SCHEMA.COLUMNS COLUMNS,INFORMATION_SCHEMA.TABLES TABLES
    WHERE COLUMNS.TABLE_NAME=TABLES.TABLE_NAME AND
    UPPER(COLUMNS.COLUMN_NAME)=UPPER('FirstName'))>0)
PRINT 'Exists'
ELSE
PRINT 'Does not exist'