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?
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
Column does not exists.
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
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
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.
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
if you want to drop column if it exists (SQL Server 2016 or above)
ALTER TABLE TableName DROP COLUMN IF EXISTS ColumnName
Subscribe to our weekly Newsletter & Keep getting latest article/questions in your inbox weekly