Drop column if exists in SQL Server 2008 r2

Question

I am using SQL Server 2008 R2.

I want to drop the column if it is already exists in the table else not throw any error.

Tried:

ALTER TABLE Emp DROP COLUMN IF EXISTS Lname;

Error:

Incorrect syntax near the keyword 'IF'.

By searching I came to know that, this option is available from 2016.

What is the alternative in the SQL Server 2008 R2?

Best Answer

From the MSDN social documentation, we can try:

IF EXISTS (SELECT 1 FROM sys.objects oINNER JOIN sys.columns c ON o.object_id = c.object_idWHERE o.name = 'Emp' AND c.name = 'Lname')ALTER TABLE dbo.Emp DROP COLUMN Lname;

I followed this way.

IF COL_LENGTH (N'{Your_TableName}', N'{Your_ColumnName}') IS NOT NULLBEGINALTER TABLE {Your_TableName}DROP COLUMN {Your_ColumnName};`END GO

Accepted Answer

IF EXISTS (SELECT 1FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = 'Emp'AND COLUMN_NAME = 'Lname'AND TABLE_SCHEMA='DBO')BEGINALTER TABLE EmpDROP COLUMN LnameENDGO

Drop column if exists in SQL Server 2008 r2

Best Answer

Random Posts