>Is there a way to determine in code the name of the table a view is based on? ...and what if a view is based on a view which is based on a table? Can I still get the original base table name? Thanks!
>
>Regards, Renoir

********************************************************************
*  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 )