There are several ways you can use to identify database object in sql statement.
1. Using TCustomSqlStatement.Fields and TCustomSqlStatement.Tables to access fields and tables information in sql.
Only fields in select list and tables in from clause are listed in these 2 properties.
Fields in TCustomSqlStatement.Fields may not connect to columns in database directly, take this sql for example:
select f1+f2 from t1
there is one field in select list: f1+f2, which includes 2 columns f1 and f2. Please check TLzField.FieldType for type of field.
Tables in TCustomSqlStatement.Tables may not connect to tables in database directly, take this sql for example:
select f1 from (select f2 from t1)
Table in from clause is a subquery which is not a physical table in database.Please check TLzTable.TableType for type of table.
2. Using TCustomSqlStatement.OnFieldToken Event and TCustomSqlStatement.OnTableToken Event to access fields and tables information in sql.
During parsing sql statement, TCustomSqlStatement.OnFieldToken Event was fired when a source token represents a column was found, and TCustomSqlStatement.OnTableToken Event was fired when a source token represents a table wasa found.
You can find all columns and tables in sql statement that not limited in select list and from clause. But only column and table will be found, not other database object such as function will be found in current version. This limitation will be removed when more database objects event were introduced in new version.
3. Iterate source tokens in sql statement,and check TSourceToken.DBObjType to find out database object in this sql.
You can only iterate source tokens in any parse tree nodes inherited from TLz_Node Class, such as Where clause, and find out database objects only in where clause as you needed.
Using this method, you can find all database objects besides column and table.(This can be used to identify what columns are used in SQL predicates in database, to help identify index and partition key candidates.)
4. Using Database object tokens property in TCustomSqlStatement such as ServerTokens,DatabaseTokens,SchemaTokens,TableTokens.
Database object tokens in TCustomSqlStatement such as ServerTokens,DatabaseTokens,SchemaTokens,TableTokens store all original information about different database objects in sql statement. You can find everything you need in those properties.
Meanwhile, information in those properties are grouped by each sql statement, and not sorted, same object may occurs serval time. Sometimes, we want to access thsse database objects in a script level, grouped by server,database,schema with sourted information.
So, we prepare all these raw information in TCustomSqlStatement into a new class: TLzDBObject, along with TLzDBObjectList, after that you have a more convenience way to access database object in sql scripts.
For how to achieve this, please check this demo: