SQL Server 添加或修改列

標籤：ar   sp   on   ad   bs   ef   as   sql   har   

－－添加列

IF NOT EXISTS(SELECT * FROM SYSCOLUMNS WHERE id=OBJECT_ID(‘tbl_TranWOAndCoCHistoryByRAILASSURE‘) AND name=‘CreatedDate‘)
BEGIN

ALTER TABLE tbl_TranWOAndCoCHistoryByRAILASSURE
ADD CreatedDate DATETIME NOT NULL DEFAULT GETDATE()

END

 

－－修改列

IF  EXISTS(SELECT * FROM SYSCOLUMNS WHERE id=OBJECT_ID(‘tbl_TranWOAndCoCHistoryByRAILASSURE‘) AND name=‘CreatedDate‘)
BEGIN

ALTER TABLE tbl_TranWOAndCoCHistoryByRAILASSURE
ALTER COLUMN CreatedDate NVARCHAR(10)

END

 

－－1.刪除列，先刪除預設值約束名

IF  EXISTS(SELECT * FROM SYSCOLUMNS WHERE id=OBJECT_ID(‘tbl_TranWOAndCoCHistoryByRAILASSURE‘) AND name=‘CreatedDate‘)
BEGIN

ALTER TABLE tbl_TranWOAndCoCHistoryByRAILASSURE
DROP constraint DF__tbl_TranW__CreatedDate__359DCDD0

END

 

－－2.刪除列

IF  EXISTS(SELECT * FROM SYSCOLUMNS WHERE id=OBJECT_ID(‘tbl_TranWOAndCoCHistoryByRAILASSURE‘) AND name=‘CreatedDate‘)
BEGIN

ALTER TABLE tbl_TranWOAndCoCHistoryByRAILASSURE
DROP COLUMN CreatedDate

END

 

SQL Server 添加或修改列