How to Drop a Column in SQL?

Limitations / Restrictions

In SQL Server, removing a column with a CHECK constraint directly from a table is not permitted. When attempting to drop such a column, you must first eliminate any constraints associated with it. This includes PRIMARY KEY, FOREIGN KEY, and other dependencies. While SQL Server does not allow direct dropping of columns with such dependencies, you can accomplish this task using alternative methods such as the Table Designer tool. Alternatively, you can leverage Object Explorer or Transact-SQL to manage and drop the necessary dependencies before removing the column.

In SQL Server, there are primarily two methods for dropping columns from a table:

• Transact-SQL
• SQL Server Management Studio (SSMS)

Transact-SQL

Syntax

In this syntax:

1. Begin by specifying the name of the table from which you intend to remove columns.
2. Then, identify the name of the column that you wish to delete.

You can delete several columns from a table at once by listing their names separated by commas after the DROP COLUMN part of the command. This lets you remove multiple columns in a single operation. The following syntax exemplifies this process:

DROP COLUMN Example

Consider the below example:

The "Employees" table comprises columns for EmployeeId, FullName, Age, Gender, Department, and Salary. EmployeeId is an auto-incrementing integer serving as the primary key. FullName is a variable character field for storing employee names. Age is an integer with a CHECK constraint ensuring a minimum age of 18. Gender is a single-character field representing the employee's gender, likely 'M' for male and 'F' for female. Department is a variable character field storing department information Salary is a decimal field for recording employee salaries.

• Suppose we want to drop Gender column. We can use the below syntax to remove it:

• In certain scenarios, there might be a need to remove multiple columns from a table simultaneously. For instance, to delete both the 'Department' and 'Salary' columns from the 'Employees' table, you can execute the following statement

• As evident from the table definition, the 'Age' column is associated with a CHECK constraint. Consequently, attempting to delete it directly will result in an error. The following command illustrates this scenario:
  • Output: Error message

    The object `CHK_EmployeeAge` is dependent on column 'Age'.
    ALTER TABLE Employees DROP Column Age failed because one or more objects access this column.     
    

  Before deleting the 'Age' column, it's necessary to remove its associated CHECK constraint. This can be achieved by executing the following statement:
  Then use the above command to delete the 'Age' column.

SQL Server Management Studio (SSMS)

It is a graphical tool for connecting and managing SQL Server databases on Windows systems. It offers various methods for dropping columns from tables, including:

1. Table Designer
2. Object Explorer

Drop a column using Table Designer

The process to drop a column in sql using Table Designer involves the following steps:

• Step 1: Navigate to the desired table in Object Explorer under Databases -> Tables.
• Step 2: Access the desired table by right-clicking on it and choosing the "Design" option from the context menu.
• Step 3: The table will be displayed in design mode.
• Step 4: Right-click on the column you wish to delete and choose "Delete Column" from the menu.
• Step 5: If prompted to confirm deletion and handle related relationships, click "Yes."

Drop a column using Object Explorer

To drop a column in sql using Object Explorer, follow these steps:

• Step 1: Navigate to the target table under Databases -> Tables.
• Step 2: Expand the table, then expand the Columns folder.
• Step 3: Right-click on the column you wish to remove and select the "Delete" option.
• Step 4: In the Delete Object dialog box, click "OK." If the column has constraints or dependencies, an error message will appear. Resolve this by deleting the referenced constraints.
• Step 5: Refresh the database or table to complete the deletion process.