Execute the following Microsoft T-SQL example scripts in SQL Server Management Studio Query Editor to demonstrate deleting (dropping) a column from a table.

------------
-- Microsoft SQL Server T-SQL delete column from table - drop column
------------
/******* 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
*/
 
-- Cleanup
DROP TABLE tempdb.dbo.Product

DROP TABLE tempdb.dbo.ProductSubcategory

------------