Parsing SQL DDl

[mike65]

In the program I am trying to write, I need to extract table names, attribute names, data types from a text file which is actually a SQL DDL.The text file is something like this

CREATE TABLE SYSTEM.AQ$_INTERNET_AGENT (
AGENT_NAME VARCHAR2(30) NOT NULL,
PROTOCOL NUMBER(0) NOT NULL,
SPARE1 VARCHAR2(128),
PRIMARY KEY (AGENT_NAME)
);
CREATE TABLE SYSTEM.AQ$_INTERNET_AGENT_PRIVS (
AGENT_NAME VARCHAR2(30) NOT NULL,
DB_USERNAME VARCHAR2(30) NOT NULL
);

Is there an easy way to extract table names and attribute names from such a text file?
Thanks.

[Alex Papadimoulis]

There is no easy way to do this.

Based on VARCHAR2 in your code, it looks like you're using Oracle, and their CREATE TABLE statement is a complete beast.

In order for you to get table names, column names, etc., you would first need to write a parser. There are some great tools avaiable for this (GOLD Parsing System comes to mind), but it's still a ton of work.

[damaha]

I think this article is what you are looking for:

http://www.dpriver.com/blog/list-of-demos-illustrate-how-to-use-general-sql-parser/analyzing-ddl-statement/

It illustrates how to analyze create table statement, extract table name, attribute names, data types, fetch Primary Key, Foreign Key, Unique Columns.