Getting the name of the column constraint 2

[rewdee]

I would like to change the data type of a column from money to char(1). Through experimenting I found that the best way to do this (and correct me if I'm wrong) is to drop the column (I don't care about the data in the column) and add a column with the same name. However, there's a constraint (default value of 0) that I need to first drop. I've tried this code:

    ALTER TABLE GRP DROP Constraint DF_GRP_CoPayDispFee
    ALTER TABLE GRP DROP COLUMN CoPayDispFee

    ALTER TABLE GRP
    ADD  CoPayDispFee CHAR(1) NULL
    DEFAULT 'F' WITH VALUES 

    Update GRP Set CoPayDispFee = 'F'

The difficulty I'm having is:
[ul]
[li]My default value constraint isn't always called 'DF_GRP_CoPayDispFee'. How can I retrieve the name of the constraint on CoPayDispFee?[/li]
[li]Even when the name of the constraint is 'DF_GRP_CoPayDispFee', the update statement fails. It almost seems like I need to call refresh or something because the error message indicates that it is still a money field. How can I call 'refresh' or something?[/li]
[/ul]

Thanks,
Rewdee

 

[NoCoolHandle]

select name,text from sysconstraints sc join sysobjects so on sc.constid = so.id join syscomments scm on so.id = scm.id
where type='d'

will list the name and constraint value

then all you need to do is take the name and script a drop

e.g.
The following code creates a table with a default that has a value of 45. the script then finds the name of (any) default that has a value of 45 and drops (the first one)
you could enhance it using the objectid of your table..

Create Table a (c1 int default 45)
exec sp_helpconstraint a
declare @name varchar(300)
select @name = name from sysconstraints sc join sysobjects so on sc.constid = so.id join syscomments scm on so.id = scm.id 
where type='d' and cast(text as varchar(300)) ='(45)'
exec ('alter table a drop constraint ' +  @name)
exec sp_helpconstraint a
drop table a

HTH

Rob

 

[vongrunt]

To get constraint(s) for exact table/column, use:

select B.name
from sysconstraints A 
inner join sysobjects B on A.constid=B.id
inner join sysobjects C on A.id=C.id and C.name='myTable'
inner join syscolumns D on A.id=D.id and A.colid=D.colid and D.name='myColumn'
-- and B.xtype = 'D'

 

[rewdee]

Thanks VonGrunt and NoCoolHandle the following works nicely.

    declare @name varchar(300)
    
    select @name=B.name
    from sysconstraints A
         inner join sysobjects B on A.constid=B.id
         inner join sysobjects C on A.id=C.id and C.name='GRP'
         inner join syscolumns D on A.id=D.id and A.colid=D.colid and D.name='CoPayDispFee'
    
    exec ('alter table GRP drop constraint ' +  @name)
    exec sp_helpconstraint GRP
 
    
    ALTER TABLE GRP DROP COLUMN CoPayDispFee

    ALTER TABLE GRP
    ADD  CoPayDispFee CHAR(1) NULL
    DEFAULT 'F' WITH VALUES 
    
    exec sp_helpconstraint GRP
    Update GRP Set CoPayDispFee = 'F'