Comparing column defaults between 2 databases

Question

Post reply

Comparing column defaults between 2 databases

mark_green

SSC Rookie

Points: 42
More actions
February 1, 2010 at 4:53 pm

#144743

Hi all,
got a strange request thrown across my desk.
there are 2 databases (lets use db1 and db2) with the same schema except for the column defaults and it has been requested for a script to be developed to compare the column defaults between db1 and db2 and update the column defaults of db2 if they don't match db1.
I was considering using the sp_columns_rowset2 stored proc to dump the column information from db1 and db2 into temporary tables and comparing these to flag which columns are different. Then from there outputting the differences to another table which i could use to dynamically build update statements to modify the column defaults in db2.
has anyone seen a script or an easy method of completing the task??? trying not to reinvent the wheel here.
cheers

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I'd use a tool like RedGate SQLCompare or Apex SQLDiff.

If you have to have a script, this should get you started:

SELECT

T.name AS table_name,

DC.name AS default_name,

DC.definition,

C.name AS column_Name,

'Alter Table ' + T.name + ' Add Constraint ' + DC.name + ' Default ' + DC.definition + ' For ' + C.name

FROM

db1.sys.default_constraints AS DC JOIN

db1.sys.tables T ON

DC.parent_object_id = T.object_id JOIN

db1.sys.columns AS C ON

T.object_id = C.object_id AND

DC.parent_column_id = C.column_id

EXCEPT

SELECT

T.name AS table_name,

DC.name AS default_name,

DC.definition,

C.name AS column_Name,

'Alter Table ' + T.name + ' Add Constraint ' + DC.name + ' Default ' + DC.definition + ' For ' + C.name

FROM

db2.sys.default_constraints AS DC JOIN

db2.sys.tables T ON

DC.parent_object_id = T.object_id JOIN

db2.sys.columns AS C ON

T.object_id = C.object_id AND

DC.parent_column_id = C.column_id

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

mark_green SSC Rookie Points: 42 More actions · Answer 2

Thanks Jack, greatly appreciated. This will go a long way to solving the problem.

davros30 SSC Veteran Points: 273 More actions · Answer 3

There is also a cheaper alternative to RedGate and Apex SQL Diff. These are both excellent tools but are quite expensive. Alternatively there is a tool for around $20 US that will do the job for you. It even has a free trial with limited functionality but not time limits. Check out SQLC[/url]