1

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

1 Answer 1

2

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
1
  • Thanks. It's working. I have tested it. though we need to add many select statements in case we are dropping some 10 or 20 columns. but I am happy and accepting it as an answer. Thank you. Oct 1, 2019 at 8:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.