change default binding programmatically

  • For every table in the database that has a field where the default binding is currently getdate(), I need to change it to SYSDATETIMEOFFSET()

    Anyone already have a script written?

    My boss designed a lovely 2008 DB backend for a dot net app we're developing... however he default bound a buncha fields (datetimeoffset(7)) to getdate(). Shoulda been SYSDATETIMEOFFSET()

    I'd do it by hand, but DB is freakin' huge. As yet unused, so no prob with data. Don't need to update that. Just change the default binding.

    TIA,

    [font="Courier New"]ZenDada[/font]

  • Just whipped this up, so it is not tested very well but it should do the trick.

    declare @defname varchar(100)

    declare @tabname varchar(100)

    declare @colname varchar(100)

    declare @sql nvarchar(max)

    DECLARE _def CURSOR

    FORselect a.name, b.name, c.name from sys.default_constraints a

    inner join sys.tables b

    on a.parent_object_id = b.object_id

    inner join sys.columns c

    on a.parent_column_id = c.column_id

    and b.object_id = c.object_id

    where definition = '(getdate())'

    -- Open the cursor.

    OPEN _def

    -- Loop through the partitions.

    WHILE (1=1)

    BEGIN

    FETCH NEXT FROM _def

    INTO @defname, @tabname, @colname

    IF @@FETCH_STATUS < 0

    BREAK

    set @sql ='

    alter table '+@tabname+'

    drop constraint '+@defname+'

    alter table '+@tabname+'

    add constraint '+@defname+' default (SYSDATETIMEOFFSET()) for ['+@colname +']

    '

    --print @sql

    exec sp_executesql @sql

    END

    CLOSE _def

    DEALLOCATE _def

    GO

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • Works! SWEET!

    [font="Courier New"]ZenDada[/font]

  • Minor correction, you might want to do a left outer join on sys.columns, instead of a inner join, since not all check constraints will be bound to a column. Constraints checking multiple columns wouldn't have any corresponding columns.

  • this is for default constraints thus there will always be a column associated with them.

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • Robert klimes (7/30/2009)


    this is for default constraints thus there will always be a column associated with them.

    exactly right. and just what i needed.

    [font="Courier New"]ZenDada[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

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