how to compare/validate sql schema

Question

I'm looking for a way to validate the SQL schema on a production DB after updating an application version. If the application does not match the DB schema version, there should be a way to warn the user and list the changes needed.

Is there a tool or a framework (to use programatically) with built-in features to do that? Or is there some simple algorithm to run this comparison?

Update: Red gate lists "from $395". Anything free? Or more foolproof than just keeping the version number?

Answer 1

I hope I can help - this is the article I suggest reading:

Compare SQL Server database schemas automatically

It describes how you can automate the SQL Server schema comparison and synchronization process using T-SQL, SSMS or a third party tool.

Answer 2

You didn't mention which RDMBS you're using: if the INFORMATION SCHEMA views are available in your RDBMS, and if you can reference both schemas from the same host, you can query the INFORMATION SCHEMA views to identify differences in: -tables -columns -column types -constraints (e.g. primary keys, unique constraints, foreign keys, etc)

I've written a set of queries for exactly this purpose on SQL Server for a past job - it worked well to identify differences. Many of the queries were using LEFT JOINs with IS NULL to check for the absence of expected items, others were comparing things like column types or constraint names.

It's a little tedious, but its possible.

Answer 3

Which RDBMS is this, and how complex are the potential changes?

Maybe this is just a matter of comparing row counts and index counts for each table -- if you have trigger and stored procedure versions to worry about also then you need something more industrial

Answer 4

SQL Compare by Red Gate.

Answer 5

If you are looking for a tool that can compare two databases and show you the difference Red Gate makes SQL Compare

Answer 6

Make a table and store your version number in there. Just make sure you update it as necessary.

CREATE TABLE version (
    version VARCHAR(255) NOT NULL
)
INSERT INTO version VALUES ('v1.0');

You can then check the version number stored in the database matches the application code during your app's setup or wherever is convenient.