Post reply

DROP DEFAULT CONSTRAINT problem

  • May 22, 2006 at 1:59 am

    #366257

    i've added a default constraint withouth adding the name .. in several databeses (on a column, in a table).

    something like

    ALTER TABLE X

    ADD COLUMN y int DEFAULT 19

    GO

    Now i need to change the default from script (not Enterpr.Manager). But to change it , i need to drop it ..

    the drop constraint syntax requires the name .. but i dont know the generated name for the default constraint.

    How can i drop the constraint or better .. replace the default value?

  • May 22, 2006 at 3:54 am

    #639182

    run the following

    select name from sysobjects where name like '%mytable%'

    you should see a list of all the constraints in there somewhere. the create statement will have defaulted it to a DF__tablename__fieldname___uniqueid format

    i ran the following

    ALTER TABLE dbo.Department add A int DEFAULT '1'

    then

    select * from sysobjects where name like '%department%

    - this new constraint was in the system

    DF__Department__A__3DB3258D

    MVDBA

  • May 22, 2006 at 5:27 am

    #639200

    thank you,

    i never thought to query sysobjects for the default

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply