sql-serversql-server-2008-r2

Drop column if exists in SQL Server 2008 r2

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?

Solution 
  • IF EXISTS (SELECT 1
               FROM   INFORMATION_SCHEMA.COLUMNS
               WHERE  TABLE_NAME = 'Emp'
                      AND COLUMN_NAME = 'Lname'
                      AND TABLE_SCHEMA='DBO')
  BEGIN
      ALTER TABLE Emp
        DROP COLUMN Lname
  END
GO