-----------------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
改變TABLE 欄位
最新推荐文章于 2023-03-15 13:51:51 发布