{"id":655,"date":"2010-11-30T02:13:27","date_gmt":"2010-11-30T07:13:27","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=655"},"modified":"2015-05-07T03:44:06","modified_gmt":"2015-05-07T08:44:06","slug":"get-columns-and-tables-in-sql-script-net-version","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-columns-and-tables-in-sql-script-net-version\/","title":{"rendered":"Get columns and tables in SQL script (.NET version)"},"content":{"rendered":"

THIS DEMO IS DEPRECIATED SINCE GENERAL SQL PARSER .NET VERSION 2.6.0. <\/strong>please check this new version<\/a> instead.<\/p>\n

The logic to find all tables and columns in SQL script is quite simple like this:<\/p>\n

1. Parse SQL script.<\/p>\n

2. Iterate all statements via TGSqlParser.sqlstatements and TCustomSqlStatement.ChildNodes().<\/p>\n

3. Once a statement was found, get all table tokens belong to this statement via TCustomSqlStatement.TableTokens.<\/p>\n

4. Once a table token was found, get all columns belong to this table via various TSourceToken properties explained belowed.<\/p>\n

Input SQL:<\/p>\n

\r\nSELECT e.last_name,\r\n       e.department_id,\r\n       d.department_name\r\nFROM   employees e\r\n       LEFT OUTER JOIN department d\r\n         ON ( e.department_id = d.department_id ); \r\n<\/pre>\n
Tables and columns:<\/p>\n
\r\nTables:\r\ndepartment\r\nemployees\r\n\r\nColumns\r\ndepartment.department_id\r\ndepartment.department_name\r\nemployees.department_id\r\nemployees.last_name\r\n<\/pre>\n
If you want to print columns in the same order they appear in sql statement, just append “-s” parameter at command line of this demo, then it will generate following result:<\/p>\n
\r\npos        column          table         t-alias  schema   DB  Loc                                     \r\n[  1, 10]  last_name       employees     e        \t\t\t\t\t\t\tcolumnResult\/select list                \r\n[  2, 10]  department_id   employees     e        \t\t\t\t\t\t\tcolumnResult\/select list                \r\n[  3, 10]  department_name department    d        \t\t\t\t\t\t\tcolumnResult\/select list                \r\n[  6, 17]  department_id   employees     e        \t\t\t\t\t\t\tjoin condition (e.department_id = d.department_id)\r\n[  6, 35]  department_id   department    d        \t\t\t\t\t\t\tjoin condition (e.department_id = d.department_id)\r\n<\/pre>\n
Since .NET version 2.2.1, user can set a callback function to TGSqlParser.OnMetaDatabaseTableColumn to provide detailed meta information from database to help SQL parser to determine relationship between column and tables. <\/p>\n
\r\nSELECT\r\n   Quantity,b.Time,c.Description\r\nFROM\r\n  (SELECT ID,Time FROM bTab) b\r\n  INNER JOIN aTab a on a.ID=b.ID\r\n  INNER JOIN cTab c on a.ID=c.ID\r\n<\/pre>\n
General SQL Parser can link column: Quantity to table: cTab automatically if your callback function says table: cTab includes column: Quantity. Without information provided by this callback function, General SQL Parser will link column: Quantity to table: aTab which is the first possible table.<\/p>\n
Download this demo: C# demo<\/a><\/p>\n
Java version of this document<\/a><\/p>\n
ANSI C demo<\/a><\/p>\n
In order to understand this demo listed, here are some conceptions you should know.<\/p>\n
\r\nselect table1.f1, t1.f2\r\nfrom table1 t1\r\nwhere t1.f3 = 1\r\n<\/pre>\n
Token ‘table1’<\/strong> in from clause is a declared table token<\/strong>.
\nToken ‘table1’<\/strong> in select list is a referenced table token<\/strong>.
\nToken ‘t1’<\/strong> in from clause is a declared table alias token<\/strong>.
\nToken ‘t1’<\/strong> in select list and where clause is a referenced table alias token<\/strong>. <\/p>\n
Only declared table token will be listed in TCustomSqlStatement.TableTokens, referenced table token, declared table alias token and referenced table alias token can be found in RelatedToken\/RelatedTokens property of declared table token. <\/p>\n
Temp table, table variable and declare CTE table will also be listed in TCustomSqlStatement.TableTokens.
\nDBObjType property of TSourceToken cab be used to find out those tokens. <\/p>\n
Following properties in TSourceToken Class had different meanings according to what’s kind of table type this token is. <\/p>\n