datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to delete a column from a table?

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

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

Exam Prep 70-461
Exam 70-461