3

I'm looking for a tool or an open-source project that can generate database update scripts in the following way:

  • Accept a database connection string as a config parameter
  • Connect to the specified DB and get a list of all DB objects: tables, stored procedures, constraints, etc. (ideally I'd like to customize the kinds of objects to look for - i.e. only tables or only stored procedures)
  • Generate a SQL script containing code that checks whether each of the discovered objects exists, and creates one if it doesn't exist.

I currently need to do this for a SQL Server database.

The purpose of this tool would be to periodically synchronize database structure between multiple instances.

Windows is preferable. The cheaper the better (obviously), ideally I would like to find an open-source project that does something similar. But I don't have any fixed price limit.

Improve this question
0

3 Answers 3

Reset to default
1

No, there is no tool for doing all that you ask, not with 100% success and not with complete coverage.

Missing pieces, meaning objects added later in one of the instances, is tough enough. But alterations such as changed names, altered data types, dropped columns or tables, and so on make periodic compare-to-sync impossible.

Database diff tools

Comparing two database structures is sometimes called "diff" comparisons or "diffing" as in the difference-detecting features of text editors and word processors that compare documents.

These tools are specific to particular database products as any implementation of a real database product far exceeds the scope of the SQL standard. And, as far as I have seen, none of these tools are 100% complete due to the complexities in implementing a database system.

These tools may generate a report of the difference. And some tools may generate SQL to create objects defined in one but lacking in the other. But I caution you against considering such reports or SQL to be perfect or complete.

Database migration tools

Managing the changes made to your database structure over time is called database migration tool. The purpose is to evolve your Database Schema easily and reliably across all your instances.

Two good ones in the Java world are:

The easiest way to use such a tool is at the beginning of the database. Then you record every modification of the database schema as SQL scripts. An extra table is first created to record a version number. The scripts can be run on any database instance to bring its schema up to date.

And there are ways to use such a tool on an existing databases as well.

Improve this answer
2
  • That's not quite what I'm looking for. I don't want to manually write update scripts each time I change the schema (e.g. ALTER TABLE xxx ADD COLUMN yyy). I want to be able to automatically generate a single SQL script that would update any database to the structure of my local DB (pseudo-code: IF COLUMN xxx.yyy NOT EXISTS THEN ALTER TABLE xxx ADD COLUMN yyy). I understand that such update strategy won't work in 100% of cases, but it'll be enough for my needs.
    – holdenmcgrohen
    Commented Jul 6, 2016 at 9:08
  • @holdenmcgrohen See my edits.
    – Basil Bourque
    Commented Jul 6, 2016 at 9:33
1

I would highly recommend SQL compare from Redgate:

http://www.red-gate.com/products/sql-development/sql-compare/

It seems to have the features you need. Another of their tools, 'multi script' would permit the deployment to multiple instances:

http://www.red-gate.com/products/dba/sql-multi-script/

We've been using these for a while and they've been rock solid and pretty easy to get the hang of.

Improve this answer
4
  • Redgate makes a fantastic product for this (many fantastic products actually). Our DBAs have used it for the very purpose in the question (sync environments/servers).
    – UnhandledExcepSean
    Commented Jul 6, 2016 at 13:20
  • Thanks for the advice, but I'm not really looking for a DB diff tool. I want to be able to generate a single SQL file that would hold the whole schema of a single DB instance and would then be applied to other DB instances and identically update their schemas.
    – holdenmcgrohen
    Commented Jul 6, 2016 at 13:55
  • @holdenmcgrohen - is that not a DB diff tool (by a slightly different guise)?
    – Paddy
    Commented Jul 6, 2016 at 14:48
  • @Paddy, a diff tool takes 2 (or more) databases and looks at the differences. I'm looking for a way to generate a script based on only 1 database.
    – holdenmcgrohen
    Commented Jul 6, 2016 at 14:57
1

A little late, but I will add my tip just for the record: https://www.schemacrawler.com/

It won't do the SQL generation, though, you'd have to do that yourself.

I've used it's output (as well as reflection info from JPA Entities) as input to JGraphT (create FK graph) to use topological sorting to find out where to start deleting rows without breaking FK constraints. In a perfect database world I guess cascade delete would resolve the problem, but ...

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.