| Author |
Topic |
|
dtrivedi
Posting Yak Master
153 Posts |
 Posted - 2011-05-18 : 07:54:42
|
| My boss asked me to match two diffrent databases schema. what is the easiest way to start doing this? |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2011-05-18 : 08:14:08
|
| unfortunately due to firewall issues i can't download this...is there an original way to do this? |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-18 : 08:19:15
|
| You can use the inforfation_schema viewsAlso could script the two databases (probably be object) and do a compare on the resultIn both cases ou will have to decide on what is important - e.g. index/default names - easier to ignore with the views.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2011-05-18 : 09:34:21
|
| can you be more specific? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2011-05-19 : 08:12:41
|
| no they are not on the same server. He wants to compare the schema because one of the datbases has new data that the other doesn't have |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-19 : 08:55:51
|
| Information_schema.tables gives a list of tablesInformation_schema.columns gives a list of columns....You can usea linked server to access the other server and compare.>> one of the datbases has new data that the other doesn't haveDo you want to compare data or schema?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2011-05-19 : 10:06:24
|
| the schema has changed because of the data |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-05-19 : 10:09:59
|
| You need to compare data NOT schema. You can use EXCEPT and INTERSECT for this purpose.--------------------------http://connectsql.blogspot.com/ |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-19 : 10:16:49
|
quote:
Originally posted by dtrivedi
the schema has changed because of the data
Still not sure what you want.If it's the schema changes then you can do it using the information_schema views.If it's data changes to common tables/columns then you can join on the PKs and compre the attribute column values.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Matching 2 databases schemas