>I suppose after I could not make the free tool (Atlantis) work, I have two choices, Red-Gate or VS 2010. Red-Gate SQL Diff is $399. VS 2010 Premium is much more. Thank you.

Actually you can roll your own, but it's not easy. I helped develop one such tool (not sure whether it's publishable, and also a bit specific about what it does and doesn't do). Without consulting co-conspirators, I can only outline how it works:


On your side, one piece of code receives the handle as a parameter, then calls a bunch of sql*() functions to get columns, indexes, constraints, defaults, stored procedures, triggers, functions and field descriptions. These are all in cursors, which are then copied into a dbf each, in a freshly created dbc. These are the metadata that get distributed.

The distributable part contains all these dbfs. It then goes through tables one by one, checks if the table exists. If not, builds a script to create it; if it does, checks the fields from the metadata against actual fields - adding or altering them as needed. If there's an existing index or a bound constraint involvning a changed field, drops them first and recreates them later. Then checks the indexes, re-creating or adding them as needed, checking that there can be only one clustered index. Creates defaults where defined in metadata (or on any non-nullable fields, so we don't have to worry about "field doesn't accept nulls" error later). Drops all procedures defined in metadata and creates them afresh, with a predesignated owner. Sets rights to them as they were before. Quits. Logs everything.

There's a bunch of things it doesn't do, because the app doesn't use them - foreign keys, replication, doesn't drop any obsolete fields or tables etc etc.

The time that went into this costs way more than the $400 you mention, but then this has these advantages (imaginary, I guess, because I don't know how the mentioned products work):

- it carries its metadata (basically a snapshot of the structure of the database as it should be) with itself; to create these it needs only a connection to the database set the way we want it - doesn't need a connection to any target database when it's created, nor a connection to the source database when it's applied

- it builds the scripts on the fly, by analyzing differences between the present and desired structure of the target database, so the target can be several versions old and it doesn't care

- it's a simple executable that contains everything it needs, so it can be distributed and run by people who are not programmers