Surfing The Keybord ⌨⌨⌨

declare @name nvarchar(100)
select @name = [name] from sys.objects where type = 'D' and parent_object_id = object_id('OD')

if (@name is not null)
  begin
     exec ('alter table [OD] drop constraint [' + @name +']')
  end

ALTER TABLE OD ALTER COLUMN inserDate datetime
ALTER TABLE OD ADD CONSTRAINT DF_inserDate DEFAULT GETDATE() FOR inserDate

 

這上面有兩個步驟

 

1. 刪除CONSTRAINT也就是下面這段

declare @name nvarchar(100)
select @name = [name] from sys.objects where type = 'D' and parent_object_id = object_id('OD')

if (@name is not null)
  begin
     exec ('alter table [OD] drop constraint [' + @name +']')
  end

因為目前欄位有設CONSTRAINT所以不能直接更改型態

只能先將他刪除後再更改

 

2. 更改欄位型態 也就是下面這段

ALTER TABLE OD ALTER COLUMN inserDate datetime
ALTER TABLE OD ADD CONSTRAINT DF_inserDate DEFAULT GETDATE() FOR inserDate

將inserDate改為datetime屬性後

我再把預設值加回來

 

執行上面兩段之後就可以在有預設值的欄位更改資料型態了

 

Write By Charley Chang 

---

新手發文，若有錯誤還請指教，
歡迎留言或Mail✉給我

本著作係採用創用 CC 姓名標示-非商業性-相同方式分享 4.0 國際 授權條款授權.