Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Delete Columns from a Table

 

This topic describes how to delete table columns in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL.

System_CAPS_cautionCaution

When you delete a column from a table, it and all the data it contains are deleted from the database. This action cannot be undone.

In This Topic

You cannot delete a column that has a CHECK constraint. You must first delete the constraint.

You cannot delete a column that has PRIMARY KEY or FOREIGN KEY constraints or other dependencies except when using the Table Designer. When using Object Explorer or Transact-SQL, you must first remove all dependencies on the column.

Requires ALTER permission on the table.

Arrow icon used with Back to Top link

To delete columns by using Object Explorer

  1. In Object Explorer, connect to an instance of Database Engine.

  2. In Object Explorer, right-click the table from which you want to delete columns and choose Delete.

  3. In Delete Object dialog box, click OK.

If the column contains constraints or other dependencies, an error message will display in the Delete Object dialog box. Resolve the error by deleting the referenced constraints.

To delete columns by using Table Designer

  1. In Object Explorer, right-click the table from which you want to delete columns and choose Design.

  2. Right-click the column you want to delete and choose Delete Column from the shortcut menu.

  3. If the column participates in a relationship (FOREIGN KEY or PRIMARY KEY), a message prompts you to confirm the deletion of the selected columns and their relationships. Choose Yes.

Arrow icon used with Back to Top link

To delete columns

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;
    GO
    ALTER TABLE dbo.doc_exb DROP COLUMN column_b ;
    

If the column contains constraints or other dependencies, an error message will be returned. Resolve the error by deleting the referenced constraints.

For additional examples, see ALTER TABLE (Transact-SQL).

Arrow icon used with Back to Top link

Show:
© 2015 Microsoft