******************************************************************** * Description.......: GetSqlTables - returns list of tables used in SQL (for view) * Calling Samples...: GetSqlTables(lcSQL) * Parameter List....: tcSQL * Created by........: Daniel Rouleau 11/09/2000 * Modified by.......: ******************************************************************** function GetSqlTables lparameters tcSQL #define True .t. #define False .f. local lnRetVal local lnCounter local llDiscard local lnEnd local lcExact local lnKeyWordCount local laKeywords[14] local lnLen local lnPos local lcSQL local lnStart local lnWhich lcRetVal = '' laKeywords[ 1] = ' SELECT ' laKeywords[ 2] = ' INTO ' laKeywords[ 3] = ' TO FILE ' laKeywords[ 4] = ' TO PRINTER ' laKeywords[ 5] = ' TO SCREEN ' laKeywords[ 6] = ' PREFERENCE ' laKeywords[ 7] = ' NOCONSOLE ' laKeywords[ 8] = ' PLAIN ' laKeywords[ 9] = ' NOWAIT ' laKeywords[10] = ' WHERE ' laKeywords[11] = ' GROUP BY ' laKeywords[12] = ' HAVING ' laKeywords[13] = ' UNION ' laKeywords[14] = ' ORDER BY ' lcExact = set('Exact') set exact on lcSQL = upper(tcSQL) + ' ' lcSQL = strtran(lcSQL, '(', ' ') lcSQL = strtran(lcSQL, ')', ' ') lcSQL = strtran(lcSQL, ',', ' , ') lnKeyWordCount = alen(laKeywords) do while True lnStart = at(' FROM ', lcSQL) if lnStart > 0 Then lcSQL = ltrim(substr(lcSQL, lnStart + 5)) lnLen = len(lcSQL) lnEnd = lnLen + 1 for lnCounter = 1 to lnKeyWordCount lnPos = at(laKeywords[lnCounter], lcSQL) if lnPos > 0 and lnPos < lnEnd Then lnWhich = lnCounter lnEnd = lnPos endif endfor if lnEnd < lnLen Then lcFromClause = left(lcSQL, lnEnd - 1) else lcFromClause = lcSQL endif llDiscard = False do while True lcWord = GetNextWord(@lcFromClause) do case case empty(lcWord) *-- Finished exit case lcWord = 'FORCE' *-- Ignore keyword case lcWord = ',' *-- Next word will be a table name llDiscard = False case lcWord = 'ON' *-- Next few words represent an expression. llDiscard = True case lcWord = 'AS' *-- Ignore keyword case inlist(lcWord, 'LEFT', 'RIGHT', 'FULL', 'INNER', 'OUTER') *-- Everything up to the JOIN keyword is *-- to be discarded llDiscard = True case lcWord = 'JOIN' llDiscard = False otherwise if not llDiscard Then *-- This word is a tablename; must set discard next word *-- not a keyword because it will be a local alias if at(',' + lcWord, lcRetVal) = 0 Then lcRetVal = lcRetVal + ',' + lcWord endif llDiscard = True endif endcase enddo else exit endif enddo if not empty(lcRetVal) Then lcRetVal = substr(lcRetVal, 2) endif set exact &lcExact return lcRetVal *********************************** function GetNextWord(tcString) local lcRetVal local lnCounter local lnLen lcRetVal = '' tcString = ltrim(tcString) + ' ' lnLen = len(tcString) for lnCounter = 1 to lnLen lcChar = substr(tcString, lnCounter, 1) if lcChar == ' ' Then tcString = substr(tcString, lnCounter) exit else lcRetVal = lcRetVal + lcChar endif endfor return ( lcRetVal )