Post reply

how to drop default from column?

  • December 17, 2007 at 4:20 am

    #63132

    Hi, it`s a big problem to me

    i want to drop defualt value from 'not null' column by sql script

    or in fact i want to add new column which don`t allows nulls but with out default value, to the existing table

    please help me

    Thanks 🙂

  • December 17, 2007 at 4:33 am

    #760646

    Hi

    to drop a default value

    ALTER TABLE tablename

    DROP CONSTRAINT constraintname

    constraint name is the name of the default constraint.

    we cannot create a new not null column without having a default constraint. i.e either the new column is nullable or it is not null with a default constraint.

    what u can do is create a new nullable column, update the column with a value and then make the column not null.

    "Keep Trying"

  • December 17, 2007 at 4:39 am

    #760647

    o thanks can you give me example?

    i have table cisINI and i do

    ALTER TABLE cisINI ADD myCol int not null DEFAULT 1

    and i want do remove this default value

    Thanks

  • December 17, 2007 at 4:56 am

    #760650

    Here is script which removes default from column.

    declare @sql varchar(8000)

    declare @cTableName varchar(100)

    declare @cColumnName varchar(100)

    set @cColumnName = 'myCol'

    set @cTableName = 'cisINI'

    select @sql = 'alter table ' + @cTableName + ' drop constraint ' + so.name

    from sysobjects so join syscolumns sc on

    sc.id = so.parent_obj and

    sc.colid = so.uid

    where so.parent_obj = object_id(@cTableName) and so.xtype = 'D'

    and sc.name = @cColumnName

    exec (@sql)

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

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