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?

Answer 1

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

Answer 2

From the MSDN social documentation, we can try:

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