If I have two sql server databases that started out with identical
table/key/index structures, but were not properly kept in sync, is
there any way I can generate a table change script to essentially
'diff' the two databases and come up with table change scripts to
bring one in line with the other?

An answer to this age-old question of mine would make me very
happy...!

Brian

ERwin will do this easily for you (reverse engineer one database and then do
a compare with the other). PowerDesigner and ER/Studio will probably also
do it.

Downside? They're not free...

To find out what's different, you could do something like this:

select so.name, sc.name, sc.type from sysobject so inner join syscolumns sc
on so.id = sc.id
order by so.name, sc.name

in both databases, paste the output into .TXT files and then do a compare on
the .TXT files (WINDIFF utility) to get started. You'll have to generate
the change scripts by hand, of course.

You could completely script the databases and then WINDIFF the scripts.
However, the scripting order might be different between the two databases
and this may muddy the waters (you could rearrange the scripts by hand to
resolve some ordering problems).

If you have a little money, see if you can find a database consultant with
access to ERwin or one of the other tools to come in for a couple of hours
and use his tools to generate the scripts for you. It might save a lot of
time. You could ask him to print diagrams, too, which might be helpful down
the road.

If you have a fair amount of cash, consider buying one of these tools
yourself - they're very, very handy. ER/Studio used to offer a freely
downloadable demo; don't know about ERwin or PowerDesigner. It seems to me
that ERwin is something like $4000. I think ER/Studion was less, don't
recall about PowerDesigner.

DesktopDBA, if it's still around, may also offer some capability this way.

I suppose you could check C|Net, SQLServerCentral or some of the other
SQL-oriented group sites for freely downloadable utilities, too.

"Brian McGee" <brian.mcgee@Sentrio.com> wrote in message
news:831a513c.0309110332.2184b751@posting.google.c om...
> If I have two sql server databases that started out with identical
> table/key/index structures, but were not properly kept in sync, is
> there any way I can generate a table change script to essentially
> 'diff' the two databases and come up with table change scripts to
> bring one in line with the other?
>
> An answer to this age-old question of mine would make me very
> happy...!
>
> Brian

In article <831a513c.0309110332.2184b751@posting.google.com >,
brian.mcgee@Sentrio.com says...
> If I have two sql server databases that started out with identical
> table/key/index structures, but were not properly kept in sync, is
> there any way I can generate a table change script to essentially
> 'diff' the two databases and come up with table change scripts to
> bring one in line with the other?

I like Red-gate Softwares "SQL Tools" product for that.
(http://www.red-gate.com) You can get a single-user license for the SQL
Compare portion of the product for about $200. That would bring the
table definitions in line. If you also want scripts to modify the
contents of the tables, that's another $200. Of course, at that point
you're better off with the bundle, which is $350 and includes DTS
Compare which diffs server settings, DTS packages, jobs and logins.

-- Rick

P.S. No affiliation at all with Red-Gate software but their product
saved my cojones once, so I'm just passing on my experience.