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