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.
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
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) );
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