July 29, 2009 at 2:09 pm
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]
July 29, 2009 at 2:32 pm
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
FOR select 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
July 29, 2009 at 2:55 pm
Works! SWEET!
[font="Courier New"]ZenDada[/font]
July 30, 2009 at 9:01 am
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.
July 30, 2009 at 12:18 pm
July 31, 2009 at 11:08 am
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