ALTER TABLE

--alter column:

ALTER TABLE Type_Tbl   ALTER COLUMN [Type_Name] [nvarchar] (50) NULL

--drop column:

ALTER TABLE CI_Waiting_Form DROP COLUMN PlantModelParentId

--add column:

ALTER TABLE dbo.CI_FTag ADD EndDate datetime NULL

-- rename column:
EXEC sp_rename 'dbo.TableName.CurrentColumnName', 'NewColumnName', 'COLUMN'
-- 'COLUMN' is the value for the parameter @objtype (of sp_rename)
GO

--new column with default value:

ALTER TABLE CI_Waiting_Form ADD DestroyingApparentlyLab bit NULL
GO
ALTER TABLE CI_Waiting_Form ADD CONSTRAINT
 DF_CI_Waiting_Form_DestroyingApparentlyLab DEFAULT 0 FOR DestroyingApparentlyLab
GO
UPDATE CI_Waiting_Form SET DestroyingApparentlyLab = 0
GO


-- rename table:
EXEC sp_rename 'dbo.TableName', 'NewTableName';
GO
--Caution: Changing any part of an object name could break scripts and stored procedures!!

-- Alter table Schema
ALTER SCHEMA newSchema TRANSFER orgSchema.StamTable
http://copypastenet.blogspot.com/2011/04/alter-schema.html

--Drop primary key:
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND name = N'PK_Name')
ALTER TABLE dbo.TableName DROP CONSTRAINT PK_Name
GO

-- Drop constraint/Foreign key:
IF EXISTS
( select constid from sysconstraints
where constid=object_ID('FK_Name') and id=object_ID('TableName')
)
ALTER TABLE [dbo].[TableName] DROP constraint [FK_Name]
GO

Alter table:
http://msdn.microsoft.com/en-us/library/ms190273.aspx