Highlight & extract schema.table name from sql.

gistfile1.txt

Highlight & extract schema.table name from sql.
The result is printed on the console page. (Click circle on Build History)

Source tables : 
  FROM ?
  JOIN ?
Target tables : 
  CREATE TABLE ?
  DROP/TRUNCATE TABLE ?
  DELETE FROM ?
  INSERT INTO/OVERWRITE ?

Limitation (can't solved)
1. multiline comment: /* ... */
2. table list after from clause: from a, b, c
3. sql size: below 120k (jenkins limit)

sql highliter

set +x

[ -f input.sql ] && SQL=$(cat input.sql)

echo
echo "==== SOURCE TABLES"
echo

echo "$SQL" \
| grep -vE '^\s*--' \
| grep -viE 'delete\s+from\s+[0-9a-z_]+(\.[0-9a-z_]+)+' \
| grep -iEo 'from\s+[0-9a-z_]+\.[0-9a-z_]+|join\s+[0-9a-z_]+\.[0-9a-z_]+' \
| tr [A-Z] [a-z] \
| tr -s '[:space:]' \
| sort | uniq

echo
echo "==== TARGET TABLES"
echo

echo "$SQL" \
| grep -vE '^\s*--' \
| grep -iEo 'create\s+table\s+(if)?\s*(not)?\s*(exists)?\s*[0-9a-z_]+(\.[0-9a-z_]+)+|(drop|truncate)\s+(table)?\s*(if)?\s*(exists)?\s*[0-9a-z_]+(\.[0-9a-z_]+)+|delete\s+from\s+[0-9a-z_]+(\.[0-9a-z_]+)+|insert\s+(into|overwrite)\s*(table)?\s+[0-9a-z_]+(\.[0-9a-z_]+)+' \
| tr [A-Z] [a-z] \
| tr -s '[:space:]' \
| sort | uniq
echo

echo
echo "==== INTERMEDIATE(TEMP) TABLES"
echo

echo "$SQL" \
| grep -vE '^\s*--' \
| grep -iEo 'create\s+(temp|temporary)\s*table\s+(if)?\s*(not)?\s*(exists)?\s*`?[0-9a-z_]+`?|(drop|truncate)\s+(table)?\s*(if)?\s*(exists)?\s*`?[0-9a-z_]+`?|delete\s+from\s+`?[0-9a-z_]+`?|insert\s+(into|overwrite)\s*(table)?\s+`?[0-9a-z_]+`?|from\s+`?[0-9a-z_]+`?|join\s+`?[0-9a-z_]+`?|with\s+`?[0-9a-z_]+`?' \
| grep -viE '\s(bimart|ods|ods_staging|sb_common|for|temp|edw|edw_staging)$' \
| tr [A-Z] [a-z] \
| tr -s '[:space:]' \
| sort | uniq
echo
# | grep -iE --color=always '^|\s(bimart|ods|ods_staging|sb_common|for|temp|edw)$' \

echo
echo "==== HIGHLIGHTED SQL"
echo

echo "$SQL" \
| grep -iE --color=always '^|^\s*((!--).)*(create\s+(temp|temporary)?\s*table\s+(if)?\s*(not)?\s*(exists)?\s*`?[0-9a-z_\.]+`?|(drop|truncate)\s+(table)?\s*(if)?\s*(exists)?\s*`?[0-9a-z_\.]+`?|delete\s+from\s+`?[0-9a-z_\.]+`?|insert\s+(into|overwrite)\s*(table)?\s+`?[0-9a-z_\.]+`?|from\s+`?[0-9a-z_\.]+`?|(left)*\s*(outer)*\s*join\s+`?[0-9a-z_\.]+`?|with\s+`?[0-9a-z_\.]+`?)+.*$' \
| GREP_COLORS='mt=03;32' grep -iE --color=always '^|--.*|\/\*.*|\*\/|\/\*.*\*\/'