Parsing SQL text

Does anyone know how to parse SQL Text with VB.NET?

Ex: I got a sql file "CREATE TABLE..." i want to get an array of columns and an array of data types.

Answers


Expanding on @Tomalak's post: once you have the table built you can use a DataReader to select just 1 line if you only need the schema or your actual data, then do something like this:

Dim myReader As DataReader
Dim myTable As DataTable

Dim myColumns As New Collection

myReader =  //' get your data

If myReader.HasRows Then
    myTable.Load(myReader)
    For Each col As DataColumn In myTable.Columns
        myColumns.Add(col.DataType.ToString, col.ColumnName)
    Next
End If

The collection myColumns will now have a Key of the column's name and the Value is the columns datatype. You can modify this to make 2 separate collections if you need.

Parsing a string on the other hand will involve significantly more debugging and offer lots of room for error.


It may be the easiest approach to feed that statement to an SQL Server and actually create that table in a temp database.

After that, finding out about the table structure would be easy.

All you'd have to parse out of the statement string would be the name of the table. Even better, you could simply replace it and have a known table name from the start.

Additionally, you would get the info if the statement is even valid SQL.


Please check this demo to help you Analyzing DDL statement

Input SQL:

create table new_employees
(
    employee_id  number primary key,
    first_name  varchar2(15) null,
    last_name varchar2(15) check(last_name>10),
    hire_date date default sysdate,
    dept_id number,
    dept_name varchar2(100),
    start_date timestamp(7) references scott.dept(start_date),
    end_date timestamp(7)   references dept.end_date on delete cascade,
    check (start_date>end_date),
    constraint c_name unique(first_name,last_name),
    foreign key(dept_id,dept_name) references dept(id,name)
); 

Generated output:

Table Name:new_employees
Columns:
name:employee_id
datetype:number
inline constraints:
    primary key

name:first_name
datetype:varchar2(15)
null: yes

name:last_name
datetype:varchar2(15)
inline constraints:
    check:last_name>10

name:hire_date
datetype:date
default:sysdate

name:dept_id
datetype:number

name:dept_name
datetype:varchar2(100)

name:start_date
datetype:timestamp(7)
inline constraints:
    foreign key
    referenced table:scott.dept
    referenced columns:start_date

name:end_date
datetype:timestamp(7)
inline constraints:
    foreign key
    referenced table:dept.end_date

outline constraints:
    check:start_date>end_date

    constraint name:c_name
    unique key
    columns:first_name,last_name

    foreign key
    columns:dept_id,dept_name
    referenced table:dept
    referenced columns:id,name

Need Your Help

Frame with 50 png in iPhone

iphone frame render imagenamed

I have a ViewController with 16 buttons. Each button loads a popover that show 50 frames renders in moving.

Regex Replace Between Quotations

c# .net regex

I am wondering on where to begin to perform the following replace in regex: