maybe this script works for u, keep in mind to alt...

2009-10-13T11:46:51.023-04:00

maybe this script works for u, keep in mind to alter the default values to your needs

DECLARE BY_CURSOR CURSOR FOR SELECT ( SELECT TOP 1 obj.[name] FROM [syscolumns] col JOIN [sysobjects] obj ON obj.[id] = col.[id] WHERE obj.type = 'U' AND col.id = o.id ),o2.name FROM syscolumns c JOIN sysobjects o ON c.id = o.id JOIN sysobjects o2 ON c.cdefault = o2.id WHERE c.NAME = 'CreatedBy_SID'
DECLARE ON_CURSOR CURSOR FOR SELECT ( SELECT TOP 1 obj.[name] FROM [syscolumns] col JOIN [sysobjects] obj ON obj.[id] = col.[id] WHERE obj.type = 'U' AND col.id = o.id ),o2.name FROM syscolumns c JOIN sysobjects o ON c.id = o.id JOIN sysobjects o2 ON c.cdefault = o2.id WHERE c.NAME = 'CreationDate_SID'

DECLARE @CONSTRAINT VARCHAR(4000)
DECLARE @TABLE VARCHAR(4000)

OPEN BY_CURSOR
FETCH NEXT FROM BY_CURSOR INTO @TABLE, @CONSTRAINT

WHILE @@FETCH_STATUS >= 0
BEGIN
EXEC( 'ALTER TABLE ' + @TABLE+ ' DROP CONSTRAINT ' + @CONSTRAINT )
EXEC( 'ALTER TABLE ' + @TABLE+ ' ADD CONSTRAINT ' + @CONSTRAINT + ' DEFAULT ([dbo].[FBAS_UsersID](suser_name())) FOR CreatedBy_SID' )
FETCH NEXT FROM BY_CURSOR INTO @TABLE, @CONSTRAINT
END

CLOSE BY_CURSOR
DEALLOCATE BY_CURSOR

OPEN ON_CURSOR
FETCH NEXT FROM ON_CURSOR INTO @TABLE, @CONSTRAINT

WHILE @@FETCH_STATUS >= 0
BEGIN
EXEC( 'ALTER TABLE ' + @TABLE+ ' DROP CONSTRAINT ' + @CONSTRAINT )
EXEC( 'ALTER TABLE ' + @TABLE+ ' ADD CONSTRAINT ' + @CONSTRAINT + ' DEFAULT ([dbo].[FDate_DateTime_2_ISO](getdate())) FOR CreationDate_SID' )
FETCH NEXT FROM ON_CURSOR INTO @TABLE, @CONSTRAINT
END

CLOSE ON_CURSOR
DEALLOCATE ON_CURSOR

Comments on Discovering .NET: Alter column set default doesn't work in T-SQL 200...

maybe this script works for u, keep in mind to alt...