{"id":581,"date":"2010-08-17T21:33:39","date_gmt":"2010-08-18T02:33:39","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=581"},"modified":"2011-05-29T21:16:01","modified_gmt":"2011-05-30T02:16:01","slug":"analyzing-ddl-statement","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/analyzing-ddl-statement\/","title":{"rendered":"Analyzing DDL statement"},"content":{"rendered":"

Analyze create table statement, extract table name, attribute names, data types, fetch Primary Key, Foreign Key, Unique Columns.<\/p>\n

\nHere is a create table statement:\n<\/p>\n

    \r\ncreate table new_employees\r\n(\r\n\temployee_id  number primary key,\r\n\tfirst_name  varchar2(15) null,\r\n\tlast_name varchar2(15) check(last_name>10),\r\n\thire_date date default sysdate,\r\n\tdept_id number,\r\n\tdept_name varchar2(100),\r\n\tstart_date timestamp(7) references scott.dept(start_date),\r\n\tend_date timestamp(7)   references dept.end_date on delete cascade,\r\n\tcheck (start_date>end_date),\r\n\tconstraint c_name unique(first_name,last_name),\r\n\tforeign key(dept_id,dept_name) references dept(id,name)\r\n);\r\n<\/pre>\n\nGenerate the following result by running “analyzing DLL” demo(Java version<\/a>, C# version<\/a>) against above SQL.\n<\/p>\n\r\nTable Name:new_employees\r\nColumns:\r\n\tname:employee_id\r\n\tdatetype:number\r\n\tinline constraints:\r\n\t\tprimary key\r\n\r\n\tname:first_name\r\n\tdatetype:varchar2(15)\r\n\tnull: yes\r\n\r\n\tname:last_name\r\n\tdatetype:varchar2(15)\r\n\tinline constraints:\r\n\t\tcheck:last_name>10\r\n\r\n\tname:hire_date\r\n\tdatetype:date\r\n\tdefault:sysdate\r\n\r\n\tname:dept_id\r\n\tdatetype:number\r\n\r\n\tname:dept_name\r\n\tdatetype:varchar2(100)\r\n\r\n\tname:start_date\r\n\tdatetype:timestamp(7)\r\n\tinline constraints:\r\n\t\tforeign key\r\n\t\treferenced table:scott.dept\r\n\t\treferenced columns:start_date\r\n\r\n\tname:end_date\r\n\tdatetype:timestamp(7)\r\n\tinline constraints:\r\n\t\tforeign key\r\n\t\treferenced table:dept.end_date\r\n\r\n\toutline constraints:\r\n\t\tcheck:start_date>end_date\r\n\r\n\t\tconstraint name:c_name\r\n\t\tunique key\r\n\t\tcolumns:first_name,last_name\r\n\r\n\t\tforeign key\r\n\t\tcolumns:dept_id,dept_name\r\n\t\treferenced table:dept\r\n\t\treferenced columns:id,name\r\n<\/pre>\n\nDownload this demo: Java version<\/a>, C# version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"Analyze create table statement, extract table name, attribute names, data types, fetch Primary Key, Foreign Key, Unique Columns. Here is a create table 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) ); Generate the following result by running “analyzing DLL” demo(Java version, C# version) against above SQL. 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\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":235,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"gsp_feature_page_tt.php","meta":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nHelp you to make better use of General SQL Parser<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n