Parsing Sql Create Table Statement Using Jquery?

I am writing a web app which uses SQL as input. This SQL must always be a CREATE TABLE statement, and what my app needs to do is getting the following attributes from it: Field na

Solution 1:

This isn't a complete solution, but hopefully something to get you started.

You could check the following regex:

/^CREATE\s+TABLE\s+(\S*)\s*\((.*)\)$/

It's crude, but it should match.

You could then pull out the first matched group (assuming you need the name of the table) and then the second matched group for the field list

You can then split the field list on the comma to get the information for each field. Outside of that you could get the field name by matching the first group in /^(.)\s(.)$/ ... You'd have to get a bit more creative in parsing the second half of the statement (types, lengths, null/not null, default, identity(1,1) ) but if you can figure out some sort of pattern that would always apply, I'm sure it could be done. Note that the above is also assuming that whitespace is properly trimmed, but that is easy enough.

Solution 2:

there's an OpenSource project located on Google Projects called "TrimQuery", below is an example:

var selectStatement = queryLang.parseSQL("SELECT Customer.* FROM Customer");

This has great support for the general SQL Languages such as JOINS and SELECT item.*,relation.other FROM X

Hope this helps.

Solution 3:

I had a task to make a hint for the Notepad ++ editor for sql. Therefore, I made parsing of dumps of the database structure. Result of work: https://github.com/trdm/jn-npp-scripts/blob/master/includes/IntellSql.js

Use:

var vQa = new CSqlDumpAnalizer();
vQa.setText(textSql)
vQa.parse();
vQa.printTables();