請教:用SQL指令將金額類型字段改為數值型為什麼不行!!!

[scott21cn]

alter table abc alter column Amount numeric(18,6)

出錯:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF_ABC_Amount' is dependent on column 'Amount'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN Amount failed because one or more objects access this column.

我想用SQL指令大批量將金額類型改為數值型,怎麼辦?
請高手指點,多謝!

[zjcxc]

alter table abc drop constraint DF_ABC_Amount
alter table abc alter column Amount numeric(18,6)
alter table abc add constraint DF_ABC_Amount default(0) for Amount

[scott21cn]

不行,報錯:
Server: Msg 3728, Level 16, State 1, Line 1
'DF_ABC_Amount' is not a constraint.
Server: Msg 3727, Level 16, State 1, Line 1
Could not drop constraint. See previous errors.

[zjcxc]

--看看这个是什么? 然后再用对应的语句来删除它.

select xtype from sysobjects where name='DF_ABC_Amount'

[scott21cn]

再請教:我如何知道各個字段在sysobjects中所對就的名字,大部分是'DF_'+表名+字段,但有小部分字段所帶表名不一樣!

[zjcxc]

无法从名字上来判断

[scott21cn]

那如何才能將syscolumns中金額字段全部刪除
delete from syscolumns where xtype=60為什麼不能刪除?

[scott21cn]

終於解決了

SELECT SQLTEXT FROM
(SELECT o.name, c.name as tablename, SQLTEXT = 'alter table ' + c.name + ' drop constraint ' + o.name , sort=1
FROM sysobjects o inner join (SELECT id,name FROM sysobjects) as c on o.parent_obj=c.id WHERE o.id in (SELECT cdefault FROM syscolumns WHERE xtype=60)
UNION
SELECT o.name, c.name as tablename, SQLTEXT = 'GO', Sort=1 FROM sysobjects o inner join (SELECT id,name FROM sysobjects) as c
ON o.parent_obj=c.id WHERE o.id in (SELECT cdefault FROM syscolumns WHERE xtype=60)
UNION
SELECT c.name, o.name as tablename, SQLText='alter table ' + o.name + ' alter column ' + c.name + ' numeric(18,6)' +case when c.cdefault>0 then ' not null' else '' end ,sort=2
FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id WHERE (c.xtype = 60 AND o.xtype = 'U')
UNION
SELECT c.name,o.name AS tablename,SQLTEXT = 'GO',Sort=2 FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id WHERE (c.xtype = 60 AND o.xtype = 'U')
UNION
SELECT c.name, o.name as tablename, SQLText='alter table ' + o.name + ' add constraint DF_' + o.name + '_' + c.name + ' default 0 for ' + c.name ,sort=3
FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id WHERE (c.xtype = 60 AND o.xtype = 'U')
UNION
SELECT c.name,o.name AS tablename,SQLTEXT = 'GO',Sort=3 FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id WHERE (c.xtype = 60 AND o.xtype = 'U')
) AS A order by sort,tablename,name