Rename all tables in complicated sql scripts with lots of joins and sub queries, then rebuild the sql on the fly.
The main property we used to fetch table and column name, and rename them is TCustomSqlStatement.TableTokens which is type of TSourceTokenList.
Before go further details about this property, let's take a look at this sql,
select table1.f1, t1.f2 from table1 t1 where t1.f3 = 1
Token 'table1' in from clause is a declared table token.
Token 'table1' in select list is a referenced table token.
Token 't1' in from clause is a declared table alias token.
Token 't1' in select list and where clause is a referenced table alias.
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.
Temp table, table variable and declare CTE table will also be listed in TCustomSqlStatement.TableTokens. Use DBObjType property of TSourceToken to find out those tokens.
Following properties in TSourceToken Class had different meanings according to what's kind of table type this token is.
===
If table token is a declared table token, then
ParentToken: schema token if any
ChildToken is null,
RelatedToken: table alias of this declared table token.
RelatedTokens: referenced token of this declared table,
or columns attached to this declared table in-directly. (such as field in create table)
or columns not linked explicit with table like column f in this sql:
select f from t
we can distinguish column token and referenced token of this declared table in RelatedTokens by using TSourceToken.DBObjType property, column token has vlaue: TDBObjType.ttObjField
RelatedSubquery is null.
===
If table token is a referenced table token:
ParentToken: schema token if any.
ChildToken: column attached to this referenced table token.
===
If table token is a declared table alias token:
RelatedTokens: referenced tokens of this declared table alias.
RelatedSubquery, subquery if this table alias is linked to a subquery in from clause.
===
If table token is a referenced table alias token:
ChildToken: column attached to this referenced table alias token.
===
If table token is a temp table token:
RelatedTokens, column attached to this table.
===
If it's a table variable token:(DBObjType = ttobjtableTemp)
RelatedTokens, columns attach to this table.
====
If it's a declared CTE table token:(DBObjType = ttobjtablecte)
RelatedTokens: referenced table of this cte table.
fields attach to this cte table.
====
If it's a referenced CTE table token:
RelatedToken, alias of this refenced cte table.
RelatedTokens: columns attached to this referened cte table
===
If it's an alias of referenced cte table token:
RelatedTokens: columns attach to this ref cte table
tablerename.cs
tablerename.sql
Gudu software http://www.sqlparser.com
|
Send comments about this topic.
|