| Author |
Topic |
|
zoozoo
Starting Member
2 Posts |
 Posted - 2011-02-21 : 01:25:09
|
| We have got two sites with same database but one was not updated since a year ago. So how can we match the schema for the old database to the new one? Also we don't want to lose the data for the old one.thanks |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-21 : 01:30:11
|
hope the result set of the following query will help you in what you are interestedselect * from INFORMATION_SCHEMA.COLUMNS --(run it under both databases)  |
 |
|
|
zoozoo
Starting Member
2 Posts |
Posted - 2011-02-21 : 01:39:24
|
| Thanks for your reply mik_2008. But I don't just want the information about particular schema. I want to first found out whats the difference between these schemas and then update the old schema to look similar to another one. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-21 : 02:12:31
|
| dude, if you get the information about all tables, their columns , and datatypes etc .. won't it allow you to do what you want e.g. to find out whats the (common and) different between both databases And then to Do the required changes as per differed information. 1) run the query under the Query Window of New Database2) Open New query Window for Old Database paste the query and run it there once you think the information is useful for the desired operation. You can simply use joins etc to find out common and differed structure/schema info. e.g. select N.ColumnName(s) , O.ColumnName(s)from [New Database Name].INFORMATION_SCHEMA.COLUMNS N[Inner/Left/Right/Full outer] Join [Old Database Name].INFORMATION_SCHEMA.COLUMNS OOn N.table_Catalog=O.table_Catalog And .... Where .... Cheers!MIK |
|
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2011-02-21 : 11:02:53
|
If you are allowed to use third party free tools (in my company it is not easy  ) then have a look at tool DBComparer. You may find it usefulhttp://dbcomparer.com/ |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-02-23 : 01:27:52
|
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=136699
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-02-23 : 07:43:11
|
| WOOT!Another use of the schema comparer!Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-02-23 : 07:52:09
|
quote:
Originally posted by zoozoo
We have got two sites with same database but one was not updated since a year ago. So how can we match the schema for the old database to the new one? Also we don't want to lose the data for the old one.thanks
I wrote the simple script that webfred posted:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=136699If you have problems running it just ask here and I'll try n help. I think it'll do what you want to do.Regards,Charlie.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
Matching the schema for two databases