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?

Desirae

'DROP COLUMN IF EXISTS' it's mysql, if I'm not mistaken – Nino

@StanislavKundii SQL Server 2016+ supports that syntax as well. – Pitapat

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

Reprography

how we will tweak above statement if one has to drop multiple columns? Please suggest – Elspeth

@vikrant rana you'll have to wrap each column you need to drop in separate if statements – Eniwetok

@Paul O... Thanks. I will give it a try soon. – Elspeth

Answer 2

I followed this way.

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

Anodize

Answer 3

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;

Livvy

Oh... Why join? 'c.object_id = OBJECT_ID('dbo.Emp')' and Top? – Nino