SQL Server Drop Columns – Multiple Columns at the Same Time

alter-tablesql serverstored-procedures

I am currently looking some way to delete multiple columns at the same time from a given table in sql server.

One way of doing that is to use ALTER statement as shown below:

Alter Table [USER_DB].[dbo].my_Table
DROP COLUMN "ID", 
            "Name", 
            "Relationship"  ;

I am using above statement in some stored procedure to drop the columns and it's working fine. However, it creates problem when I re-ran this statement and columns are already dropped.

I found some nice solution on SO which doesn't fails even when columns are dropped.

IF EXISTS(SELECT 1
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME='my_Table'
          and column_name= 'ID'
          and Table_SChema='DBO')
  BEGIN
    ALTER TABLE my_Table
    DROP COLUMN 
     ID
  END

I tried above statement to drop multiple columns at the same time but it's not working.

I was looking something like this.

IF EXISTS(SELECT 1
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME='my_Table'
          and column_name= 'ID'
          and column_name= 'Name'
          and column_name ='Relationship'
          and Table_SChema='DBO')
  BEGIN
    ALTER TABLE my_Table
    DROP COLUMN 
     ID
    ,Name
    ,Relationship
  END

Best Answer

You're currently looking for a column_name that has different values, so you'll never pass the test. You have to search in different rows to make it work.

    IF EXISTS(SELECT 1
              FROM INFORMATION_SCHEMA.COLUMNS
              WHERE TABLE_NAME='my_Table'
              and column_name= 'ID')
AND EXISTS (SELECT 1
              FROM INFORMATION_SCHEMA.COLUMNS
              WHERE TABLE_NAME='my_Table'
              and column_name= 'Relationship')
      BEGIN
        ALTER TABLE my_Table
        DROP COLUMN 
         ID
        ,Name
        ,Relationship
      END
Related Question