Diff for tables *structures* rather than data

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...!

BrianERwin 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.

Dev. Team - Keeping Three Tiers in Sync

This may not be a MSSQL-specific question, but
I wanted to ask it here first, in case there's
a MSSQL and/or SourceSafe solution that will help.

Our dev team is having some difficulty with
keeping the nightly builds in sync with the
stored proc mods. I'm wondering if there are
some good case studies on how to avoid this
"drift". Something like genning a new DB from
checked-in SPs, etc. alongside each regular build,
then always have a paired enterprise app/database
duo that is tagged and added to a history.

FWIW, we have a 3-tier .NET/C# app, and
ADO.NET is throwing exceptions every other
day.

If the suggestion is to whip the DB guys, that
works for me as well. ;-)

Nah, there's much love there.

Thanks in advance,
~swoozWe keep all of our DDL scripts under source control. These are labeled and
built along with the app code as part of an installer custom action. We
promote all stored procedures, views, and functions along with the rest of
the app so the interfaces don't get out of sync as long as the checked-in
code is correct.

Our database installer allows either a new our upgrade installation. Even
if the 'new' install isn't needed for production, it facilitates testing
since it's the 'gold' database schema. We also include upgrade capability
as needed for production releases. The bottom line is that new and upgraded
schema are identical.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"swoozie" <swooziep@.yahoo.com> wrote in message
news:1112826148.798845.123840@.g14g2000cwa.googlegr oups.com...
> This may not be a MSSQL-specific question, but
> I wanted to ask it here first, in case there's
> a MSSQL and/or SourceSafe solution that will help.
> Our dev team is having some difficulty with
> keeping the nightly builds in sync with the
> stored proc mods. I'm wondering if there are
> some good case studies on how to avoid this
> "drift". Something like genning a new DB from
> checked-in SPs, etc. alongside each regular build,
> then always have a paired enterprise app/database
> duo that is tagged and added to a history.
> FWIW, we have a 3-tier .NET/C# app, and
> ADO.NET is throwing exceptions every other
> day.
> If the suggestion is to whip the DB guys, that
> works for me as well. ;-)
> Nah, there's much love there.
> Thanks in advance,
> ~swooz|||We use a product called DB Ghost to build our SQL Server databases
directly from SourceSafe every 10 minutes. A script checks the report
it produces for any errors and automatically emails all of the DBAs and
Development team if any are found. This covers any syntax or
dependency issues with checked in code.

If everything builds OK DB Ghost is then used to produce an upgrade
script against a restored copy of our test database. If this fails the
problem is emailed out and fixed there and then. The test database
copy is then restored again and the compare rerun - if it works then we
have a delta script that we can run in Query Analyzer against our real
test database. This same approach is used all the way to production.
The script produced also works first time - I've used other products
before but none of them come close to producing a working delta script
in the first run - I always had to correct object dependency problems
manually in Query Analyzer first.

The real beauty of this is that we only keep the delta script produced
by our overnight build which starts from a labelled baseline of all
code in VSS. This means that our SQL Code is 100% in line with our
VB6/.NET and ASP code every single time we do a release. Sure, it
doesn't get around the problem of developers not checking their changes
in properly but it does give us a great audit trail which means we get
to the root causes of those tricky test database problems real quick.

We are even thinking of losing the delta script altogether and just
letting DB Ghost do the upgrade dynamically, that way our baselined,
labelled scripts in VSS are the real 'source database' - this is
totally cool stuff.