Results 1 to 5 of 5

Thread: is there a "alter table xxxx drop default yyy" ?

  1. #1
    Join Date
    Mar 2003
    Posts
    383

    is there a "alter table xxxx drop default yyy" ?

    Hi:
    I need to change a column's datatype from tinyint to int as follows:
    alter table tableName
    alter column column1 int

    but with error <<'DF__LandMarks__color__6A50C1DA' is depending on it.>>

    However, this old default is not part of the constraint. Thus, the only way is to delete it from sysobjects.

    unfortunately, the following code I have to commented since they could only be executed line by line, not within a begin...end block.
    --exec master.dbo.sp_configure 'allow updates', 1
    --reconfigure with override
    --delete from sysobjects
    -- where name = 'DF__LandMarks__color__6A50C1DA'
    -- and type = 'D'
    --exec master.dbo.sp_configure 'allow updates', 0
    --reconfigure with override

    I need to update 10 server around 2500 databases with this change.
    I have looked INFORMATION_SCHEMA related views, but not found default related, maybe I missed something.

    thanks
    David

  2. #2
    Join Date
    Sep 2002
    Posts
    169
    I strongly recommend that you do not use the code in your post (unless you want to corrupt your databases of course).

    Defaults are actually constraints, so what you are really after is ALTER TABLE dbo.YourTable
    DROP CONSTRAINT DF__LandMarks__color__6A50C1DA

  3. #3
    Join Date
    Mar 2003
    Posts
    383
    Stephen:

    It works. The thing puzzles me is why the INFORMATION_SCHEMA.TABLE_CONSTRAINTS did not show this default, which leaded me think "it is not part of the constraint" and went to the sysobjects wrong direction.

    Does it mean the INFORMATION_SCHEMA.TABLE_CONSTRAINTS does not include the default as constraints, but the alter table drop constraint do reconganize the default?
    Not quite consistent....

    thanks for the tip.
    -D

  4. #4
    Join Date
    Feb 2003
    Posts
    1,048
    I agree. It's not consistent. A default is a constraint though. You can get it by querying the sysconstraints table. Or you could write your own version of the Table_Constraints view that includes xtype = 'D'

  5. #5
    Join Date
    Mar 2003
    Posts
    383
    Hi Rawhide and all

    thanks for the clarification.

    Now, I am using the sysobjects and syscolumns to retrieve the default name, since each database comes out with different default name for this table and the coulmn.

    p.s. I noticed from Enterprise Manager, there are some database at the table design window, the datatype of the field are still tinyint after script alter column changes. While at isql/w with sp_help tableName, the field IS changed as int. Even after 'refresh' on both database and table selection.

    -D

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •