Drop or Delete a COLUMN from a Table


Execute the following scripts in SQL Server Management Studio Query Analyzer
 to delete a column from a table.

/******* NOTE – data in dropped column will be lost *******/

USE tempdb;

MSSQL select into create table – Product & ProductSubcategory for testing

SELECT * INTO   ProductSubcategory
FROM   AdventureWorks2008.Production.ProductSubcategory
GO

— (37 row(s) affected)
— Microsoft SQL Server T-SQL add Primary Key constraint to table

ALTER TABLE dbo.ProductSubcategory
 ADD CONSTRAINT PK1 PRIMARY KEY ( ProductSubcategoryID )
GO
 
SELECT * INTO   Product
FROM   AdventureWorks2008.Production.Product
GO

  

— (504 row(s) affected)
— MSSQL add Foreign Key constraint to Product table
ALTER TABLE [dbo].[Product]
WITH NOCHECK ADD CONSTRAINT [FK1] FOREIGN KEY ( [ProductSubcategoryID] )
     REFERENCES [dbo].[ProductSubcategory]([ProductSubcategoryID]);
ALTER TABLE [dbo].[Product]  CHECK CONSTRAINT [FK1]
GO 


 
SELECT TOP ( 1 ) * FROM  Product
GO

/*  Results
 
ProductID  Name  ProductNumber  MakeFlag  FinishedGoodsFlag  Color  SafetyStockLevel  ReorderPoint  StandardCost  ListPrice  Size  SizeUnitMeasureCode  WeightUnitMeasureCode  Weight  DaysToManufacture  ProductLine  Class  Style  ProductSubcategoryID  ProductModelID  SellStartDate  SellEndDate  DiscontinuedDate  rowguid  ModifiedDate
1  Adjustable Race  AR-5381  0  0  NULL  1000  750  0.00  0.00  NULL  NULL  NULL  NULL  0  NULL  NULL  NULL  NULL  NULL  1998-06-01 00:00:00.000  NULL  NULL  694215B7-08F7-4C0D-ACB1-D734BA44C0C8  2004-03-11 10:01:36.827
*/
— MSSQL delete column – drop column from table

ALTER TABLE dbo.Product DROP COLUMN Color
GO

  

— Command(s) completed successfully.
 
— Primary Key column referenced by a Foreign Key column cannot be deleted

 

ALTER TABLE dbo.Product DROP COLUMN ProductSubcategoryID
GO

 
/* Msg 5074, Level 16, State 1, Line 1
The object ‘FK1’ is dependent on column ‘ProductSubcategoryID’.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN ProductSubcategoryID failed because one or more objects 
access this column.
*/
 
— After dropping the FK constraint, the referenced column can be dropped

 

ALTER TABLE [dbo].[Product] DROP CONSTRAINT [FK1]
ALTER TABLE dbo.Product DROP COLUMN ProductSubcategoryID
GO

— Command(s) completed successfully.
SELECT TOP ( 1 ) * FROM   Product
GO

/*  Results
 
ProductID  Name  ProductNumber  MakeFlag  FinishedGoodsFlag  SafetyStockLevel  ReorderPoint  StandardCost  ListPrice  Size  SizeUnitMeasureCode  WeightUnitMeasureCode  Weight  DaysToManufacture  ProductLine  Class  Style  ProductModelID  SellStartDate  SellEndDate  DiscontinuedDate  rowguid  ModifiedDate
1  Adjustable Race  AR-5381  0  0  1000  750  0.00  0.00  NULL  NULL  NULL  NULL  0  NULL  NULL  NULL  NULL  1998-06-01 00:00:00.000  NULL  NULL  694215B7-08F7-4C0D-ACB1-D734BA44C0C8  2004-03-11 10:01:36.827
*/
 

————————————————————————————————————————————————-
Then drop the temp tables (Clean Up)

DROP TABLE tempdb.dbo.Product

DROP TABLE tempdb.dbo.ProductSubcategory

————————————————————————————————————————————————–

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: