Skip to content

delete-columns-from-a-table.md

Latest commit

 

History

History
88 lines (58 loc) · 3.52 KB

delete-columns-from-a-table.md

File metadata and controls

88 lines (58 loc) · 3.52 KB
title Delete Columns from a Table | Microsoft Docs
ms.custom
ms.date 04/11/2017
ms.prod sql
ms.prod_service table-view-index, sql-database, sql-data-warehouse, pdw
ms.reviewer
ms.technology table-view-index
ms.topic conceptual
helpviewer_keywords
columns [SQL Server], deleting
removing columns
deleting columns
dropping columns
ms.assetid 0d8f6e4f-bc71-4fa3-8615-74249c8e072d
author stevestein
ms.author sstein
monikerRange >=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

Delete Columns from a Table

[!INCLUDEtsql-appliesto-ss2016-all-md]

This topic describes how to delete table columns in [!INCLUDEssCurrent] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].

Caution

When you delete a column from a table, it and all the data it contains are deleted.

In This Topic

Before You Begin

Limitations and Restrictions

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 [!INCLUDEtsql], you must first remove all dependencies on the column.

Security

Permissions

Requires ALTER permission on the table.

Using SQL Server Management Studio

To delete columns by using Object Explorer

  1. In Object Explorer, connect to an instance of [!INCLUDEssDE].
  2. In Object Explorer, locate the table from which you want to delete columns, and expand to expose the column names.
  3. Right-click the column that you want to delete, and choose Delete.
  4. 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.

Using Transact-SQL

To delete columns

The following example shows you how to delete a column.

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).