How to check if Columns exits in SQL Server table?


Hello, I am still learning SQL and SQL Server, so I would like to know how can I verify or check if a column exists in the database table using SQL Query in SQL Server?


Asked by:- neena
1
: 1708 At:- 2/23/2022 10:31:04 AM
SQL check if column exists in table sql sql server







2 Answers
profileImage Answered by:- bhanu

To check if columns exist in the SQL server table, I will AdventureDatabase as sample DB and table employee, and I will check if 'FullName' exists 

IF COL_LENGTH('Employee','FullName') IS NULL
BEGIN
   PRINT N'Column does not exists.';  
END

Output:

Column does not exists.

check-if-column-exists-sql-server-min.png

Now, I will if column 'Gender', which exists in real, so I have updated query to add "Is Not NULL"

IF COL_LENGTH('Employee','Gender') IS NOT NULL
BEGIN
   PRINT N'Column exists.';  
END

Output:

Column exists.

column-exists-sql-server-min.png

That's it.

Another approach

In this approach, we will add a column if it doesn't exist in the table using INFORMATION_SCHEMA.COLUMNS

IF NOT EXISTS(
  SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE 
    [TABLE_NAME] = 'Employee'
    AND [COLUMN_NAME] = 'FullName')
BEGIN
  ALTER TABLE [Employee]
    ADD [FullName] Varchar(100) NULL
END

add-column-if-doesnot-exists-min.png

The above query will add a new column "FullName" in the table, if it doesn't exists, since it wasn't available, after running the above query new columns is added.

2
At:- 2/23/2022 1:31:41 PM
Thanks for detailed answer. 0
By : neena - at :- 3/29/2022 3:27:54 PM


profileImage Answered by:- vikas_jk

Here is the simple query to check if column exists in SQL Server

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

OR

if you want to drop column if it exists (SQL Server 2016 or above)

ALTER TABLE TableName DROP COLUMN IF EXISTS ColumnName

Thanks.

0
At:- 3/28/2022 1:16:17 PM






Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use