改變TABLE 欄位

-----------------Create table-----------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ARGRMS_H]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ARGRMS_H]
GO

CREATE TABLE [dbo].[ARGRMS_H] (
  [COM_NO] [varchar] (2) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
  [F_TAXAMT] [decimal](10, 2) NOT NULL  DEFAULT (0)
)


-----------------Add Column----------------------
if not exists (select * from syscolumns where id = object_id(N'[dbo].[RPT_KIND]') and name='RP_ORDER')
 begin
   ALTER TABLE RPT_KIND
   ADD [RP_ORDER] [varchar] (2) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL CONSTRAINT [DF_RPT_KIND_RP_ORDER] DEFAULT ('0')
 end  
GO


-----------------DROP Column---------------------
if exists (select * from syscolumns where id = object_id(N'[dbo].[WSINV_H]') and name='ARV_NO')
 begin
   ALTER TABLE WSINV_H
   DROP CONSTRAINT [DF_WSINV_H_ARV_NO]
   ALTER TABLE WSINV_H
   DROP COLUMN [ARV_NO]
 end  
GO

-----------------Change Column Type----------------------
if exists (select * from syscolumns where id = object_id(N'[dbo].[ARVER_DETL]') and name='ARVT_QTY_P')
 begin
   ALTER TABLE ARVER_DETL
   DROP CONSTRAINT [DF_ARVER_DETL_ARVT_QTY_P]
   ALTER TABLE ARVER_DETL
   ALTER COLUMN [ARVT_QTY_P] [int] NOT NULL
   ALTER TABLE ARVER_DETL
   ADD CONSTRAINT [DF_ARVER_DETL_ARVT_QTY_P] DEFAULT 0 FOR [ARVT_QTY_P]
 end  
GO

------------修改欄位長度----------------
alter table APVER_D_BAT 
alter column REMARK varchar(200) 


------------把一列值為另一列------------
update test set new_lolun=old_column;
commit ;
alter table table_name drop (old_column);


--如果不知道默認值約束的名稱,需要用SQL來取得:
declare @csname varchar(100)
set @csname=''

select @csname=[name] --約束名稱
from sysobjects t
where id=(select cdefault from syscolumns where id=object_id(N'表名') and name='字段名')

--刪除約束
exec('alter table 表名 drop constraint '+@csname)


--禁用約束
exec ('alter table 表名 nocheck constraint ' + @csname)


--啟用約束
exec ('alter table 表名 check constraint ' + @csname)


--添加主鍵
if not exists (select * from syscolumns where id = object_id(N'[dbo].[SYSEFM]') and name='COM_NO')
 begin
   ALTER TABLE SYSEFM
   ADD [COM_NO] [varchar] (2) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL CONSTRAINT [PK_SYSEFM] PRIMARY KEY (COM_NO)
             CONSTRAINT [DF_SYSEFM_COM_NO] DEFAULT ('')
 end