Can anyone recommend a good SQL parsers?

Question

I am trying to write a tool that can compare a database’s schema to the SQL in an install script. Getting the information from the database is pretty straightforward but I am having a little trouble parsing the install scripts.

I have played with a few of the parsers that show up on Google but they seemed somewhat incomplete. Ideally I would like to find an open source parser that is fairly stable and has half decent documentation.

Also, I am not really concerned about types and syntax that specific to certain databases. The databases that need to be check are pretty simple.

Answer 1

General SQL Parser is decent. You'd probably found it already, but what does it not do for you since you said the SQL is pretty simple?

Answer 2

Some databases, such as postgresql, let you query the current schema. So you could go the other direction: instead of trying to take the schema and parse the SQL, you can create SQL statements that query for the fields and tables you require to see if they are in the schema in the database.

Here's an example: postgresql schema metadata

Answer 3

SQL Server may be able to do some of this for you. It provides a pretty good dependency tree, which is accessible through the SDK.

Answer 4

Powerdesigner can reverse engineer from both a live database and a script and build a difference script.

There's a 15 day free trial you could use for this time, see if it's does what you want.

Answer 5

I've used SQL Compare and it's excellent.

You could run the install script to create a new db and then compare the existing db to the new db.

Answer 6

Seriously, especially for simple databases, your SQL Server has a great SQL parser built in.

Create a schema and load it up. Compare the data, then drop the schema when you're done.

If you're afraid of running the DDL, then perhaps using one of the several, lightweight, embedded servers will do the job for you (like Derby if you run Java).

Then you get the parsed SQL and access to the tables just like you do now.

You have the database, you may as well leverage it if you can.

Answer 7

If you have DDL to create the tables, parse off the table name and prepend some generated table name for this. Depending on the SQL engine you are using, you have different ways to query the schema:

Using SQLite:

PRAGMA table_info(my_table)
PRAGMA table_info(temporary_my_table)

Or on MySQL:

 SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'my_schema'
AND (table_name LIKE '%my_table';