Use a CURSOR to loop and ALTER DEFAULT CONSTRAINTs

---

--declare variables to use in cursor
declare @tablename varchar(1000)
declare @columnname varchar(1000)
declare @defaultvalue varchar(1000)
declare @constraintname varchar(1000)
-- create the cursor
declare _iterativecursor cursor for
select tablename2, columnname2, defaultvalue2, consname2 from temp_default_list1
--open the cursor
open _iterativecursor
--fetch the next record from the cursor
fetch next from _iterativecursor into @tablename, @columnname, @defaultvalue, @constraintname
-- loop through the table cursor reading each row of data and updating table values
while @@fetch_status = 0
begin
--print @tablename2
--print @constraintname2
--print @defaultvalue2
--print @columnname2
-- we can't use alter table in a cursor so we have to hack something together by using the exec statement
EXEC ('ALTER TABLE ' + @tablename + ' ADD CONSTRAINT ' + @constraintname + ' DEFAULT ' + @defaultvalue + ' FOR ' + @Columnname)
fetch next from _iterativecursor into @tablename, @columnname, @defaultvalue, @constraintname
end
--release the cursor
close _iterativecursor
deallocate _iterativecursor

 1,122 total views