CODEWITHSUNDEEP
mysqldiff
ted.strauss
ted.strauss

Reputation: 4339

Script that takes two CREATE TABLE commands and outputs an ALTER TABLE?

Is there a script or function that can generate an ALTER TABLE (or multiple ALTERs) command based on the differences between two CREATE TABLE commands?

For example, given the following two SQL commands...

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
);

and 

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

... the output would be

ALTER TABLE Persons ADD Address varchar(255);
ALTER TABLE Persons ADD City varchar(255);

Upvotes: 1

Views: 71

Answers (1)

Razi Shaban
Razi Shaban

Reputation: 512

I don't know of any tools specifically designed to diff SQL, but it might not be too difficult to use Unix's diff to help you do this.

Running diff on the two examples you gave above gives the following output:

$ diff -d 1.txt 2.txt 
6c6,8
< );
---
> Address varchar(255),
> City varchar(255)
> );

It doesn't seem like it would be too difficult to run a regex or string formatting in your scripting language of choice to pick out where a field is being added (or, in the case of <, deleted) and filling that out as an ALTER TABLE command. It seems to me that the hardest part of that would be getting the table name, but if you do it one table at a time, that might become a fairly easy task as well.

If Python's your scripting language, there is the difflib library that might help you integrate diff into however you decided to do this.

Upvotes: 1

Related Questions