Can anyone recommend some good SQL parsers?

Question

I am trying to write a tool that can compare a database schema with SQL in a setup script. Retrieving information from a database is quite simple, but I have a little problem analyzing installation scripts.

I played with several parsers that appeared 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'm really not interested in the types and syntax specific to particular databases. The databases that need to be checked are pretty simple.

Answer 1

Generic SQL Parser is decent. You would probably find this already, but what does it not do for you, since you said that SQL is pretty simple?

Answer 2

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

Here is an example: postgresql schema metadata

Answer 3

I used SQL Compare and excellent.

You can run the installation script to create a new db and then compare the existing db with the new db.

Answer 4

SQL Server can perform some of these actions for you. It provides a pretty good dependency tree available through the SDK.

Answer 5

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

There will be a 15-day free trial that you can use during this time and see if it does what you want.

Answer 6

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

Create a diagram and load it. Compare the data, then release the diagram when done.

If you are afraid of running DDL, then perhaps using one of several lightweight built-in servers will do the job for you (for example, Derby if you run Java).

Then you get the parsed SQL and access the tables in the same way as now.

You have a database, you can also use it if you can.

Answer 7

If you have DDL for creating tables, parse the table name and add 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 in MySQL:

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