Add, Alter & Drop Field in Table

Cara buat menambah field baru atau menghapus dalam sebuah table:

ADD / Menambah
ALTER TABLE table_name ADD column_name datatype
contoh:
ALTER TABLE M_Employee add FL_LEAVE INT NOT NULL DEFAULT 0 WITH VALUES
ALTER TABLE M_Employee add EMP_REF VARCHAR(100) NULL


Change / Ubah data type
ALTER TABLE table_name ALTER COLUMN column_name datatype
contoh:
ALTER TABLE M_Employee ALTER COLUMN EMP_REF VARCHAR(50)

Delete / Hapus
ALTER TABLE table_name DROP COLUMN column_name
contoh:
ALTER TABLE M_Employee DROP COLUMN EMP_REF

Jika ada constraint, biasanya drop column gak bisa langsung, harus delete contraintnya dulu.
Ini terjadi biasanya kalau add column dengan default value tanpa definisi constraint dari awal/unnamed.
Cara deletenya:

declare @name nvarchar(32), 
    @sql nvarchar(1000)

-- find constraint name
select @name = O.name 
from sysobjects AS O
left join sysobjects AS T
    on O.parent_obj = T.id
where isnull(objectproperty(O.id,'IsMSShipped'),1) = 0
    and O.name not like '%dtproper%'
    and O.name not like 'dt[_]%'
    and T.name = 'MyTable'
    and O.name like 'DF__MyTable__MyColu%'

-- delete if found
if not @name is null
begin
    select @sql = 'ALTER TABLE [MyTable] DROP CONSTRAINT [' + @name + ']'
    execute sp_executesql @sql
end

-- do your ALTER TABLE here

ALTER TABLE table_name DROP COLUMN column_name

-- replace the constraint, Jika mau add constraint baru (ini bisa dilewati)

select @sql = 'ALTER TABLE [MyTable] ADD CONSTRAINT [' + @name + '] DEFAULT (0) FOR [MyColumn]' execute sp_executesql @sql

lebih jelasnya klik disini atau ini