Unanswered: URGENT! Need to extract default values and update similar tables
I am using SQL 2005 merge replication with a publisher managing about 45 articles(tables) with about 10 subscribers (remote servers). The problem is that we had to re-start replication from scratch and noticed that, although the publisher's tables have the default values, the subscribers did not get the default values with the initial snap shot, schema building..?!?
I now have to go over 450 tables (10 remotes SQl servers at 45 tables each) and 'reset or set' over 1,000 default values. Meanwhile, the system is down...omg...so not good.
Is there a script out there that automatically extracts the default values from a table and set it to another exact table with the same structure? any ideas?
Use this query to obtain all your default constraints:
select t.name as table_name, f.name as field_name, d.name as default_name, d.definition
from sys.default_constraints d, sys.tables t, sys.columns f
where d.parent_object_id = t.object_id
and d.object_id = f.default_object_id
Put this query in a procedure and run the ALTER TABLE statements for each row.