• Welcome, Guest. Please login.
 
September 17, 2019, 09:50:26 am

News:

Welcome to the SQLitening support forums!


Get all tables (does this work for you?)

Started by mikedoty, November 05, 2008, 12:51:27 pm

Previous topic - Next topic

mikedoty

November 05, 2008, 12:51:27 pm Last Edit: November 05, 2008, 12:55:06 pm by Mike D
SELECT name FROM
(SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
      WHERE type='table'
        ORDER BY name;

When I execute this I get no error, but no record set.

While at it, does anyone have a SQL statement that displays all tables
with all column names (and why not indexes) for extra credit?

Fred Meier

This rut will put all tables, columns, indexes, triggers, and views to a flat file.
You won't be able to compile because of the calls to my private ruts but you will get the idea.
Function MapIt() as Long

   Local llDo, llDo2 as Long
   Local llA, llB as Long
   Local llInWord as Long
   Local llFlag as Long
   Local lsA as String
   Local lsTableName as String
   Local lsSql as String
   Local lsColumn as String
   Local lsColumnName as String
   Local lsColumnAttributes as String
   Local lsColumnComments as String
   Local lsColumns as String

   ' Init stuff
   axLoadLib "axBlue"
   print# glMapFileNumber, string$(80, "=")
   print# glMapFileNumber, "Mapped on " & date$ & " at " & time$ & " for Database " & gsDatabaseName
   print# glMapFileNumber,

   ' Process each table
   slSel "Select tbl_name,sql from sqlite_master where type='table'
   do while slGetRow

      ' Save names
      lsTableName = slFN("tbl_name")
      lsSql = axReplace(slFN("sql"), $CRLF, "  ", "A")
      llFlag = %False

      ' Head it
      print# glMapFileNumber, arBuildSectionHeader(slFN("tbl_name"))
      lsA = lsSql
      arSplitText lsA, 80
      for llDo = 1 to axParseCount(lsA, $WrdMrk)
         print# glMapFileNumber, space$(3) & parse$(lsA, $WrdMrk, llDo)
      next
      print# glMapFileNumber, ""

      ' Find start of columns and save it
      regexpr " *" & lsTableName & "[ '""]*\(" in lsSql to llA, llB
      if isfalse llA then
         print# glMapFileNumber, "Can't find table name followed by (."
         iterate do
      end if
      lsColumns = rtrim$(mid$(lsSql, llA + llB))

      ' Remove trailing )
      if right$(lsColumns, 1) = ")" then
         lsColumns = trim$(left$(lsColumns, -1))
      else
         print# glMapFileNumber, "Create doesn't end with )."
         iterate do
      end if

      ' Comvert column commas to $WrdMrks
      for llDo = 1 to len(lsColumns)
         select case const$ mid$(lsColumns, llDo, 1)
         case ","
            if isfalse llInWord then mid$(lsColumns, llDo, 1) = $WrdMrk
         case "[", "]", "'", "(", ")"
            llInWord = llInWord xor 1
         case "/", "*"
            select case const$ mid$(lsColumns, llDo, 2)
            case "/*", "*/"
               llInWord = llInWord xor 1
            end select
         end select
      next

      ' Print each column
      print# glMapFileNumber, space$(3) & "==========>>> Columns"
      for llDo = 1 to axParseCount(lsColumns, $WrdMrk)
         lsColumn = ltrim$(parse$(lsColumns, $WrdMrk, llDo))

         ' Check for ending constraints
         regexpr "(unique)|(primary key) *\(" in lsColumn to llA
         if llA then
            if isfalse llFlag then
               llFlag = %True
               print# glMapFileNumber, ""
               print# glMapFileNumber, space$(3) & "==========>>> Constraints"
            end if
            print# glMapFileNumber, space$(6) & lsColumn
            iterate for
         end if
          ' Build column name and attributes
         if left$(lsColumn, 1) = "[" then
            lsColumnName = mid$(lsColumn, 2, instr(mid$(lsColumn, 2), "]") - 1)
            lsColumnAttributes = ltrim$(mid$(lsColumn, len(lsColumnName) + 3))
         elseif left$(lsColumn, 1) = """" then
            lsColumnName = mid$(lsColumn, 2, instr(mid$(lsColumn, 2), """") - 1)
            lsColumnAttributes = ltrim$(mid$(lsColumn, len(lsColumnName) + 3))
         else
            lsColumnName = parse$(lsColumn, " ", 1)
            lsColumnAttributes = ltrim$(mid$(lsColumn, len(lsColumnName) + 1))
         end if

         ' Seperate any comments within the attribures
         lsColumnComments = parse$(parse$(lsColumnAttributes, "/*", 2), "*/", 1)
         arSplitText lsColumnComments, 52
         lsColumnAttributes = parse$(lsColumnAttributes, "/*", 1) & parse$(lsColumnAttributes, "*/", 2)

         ' Move first line of comments to attributes if there are no attributes
         if isfalse len(lsColumnAttributes) then
            lsColumnAttributes = parse$(lsColumnComments, $WrdMrk, 1)
            axParseDelete lsColumnComments, 1, $WrdMrk, "W"
         end if

         ' Print it
         print# glMapFileNumber, space$(6) & iif$(len(lsColumnName) > 25, lsColumnName, axJustStr(lsColumnName, -25)) & " " & lsColumnAttributes
         for llDo2 = 1 to axParseCount(lsColumnComments, $WrdMrk)
            print# glMapFileNumber, space$(31) & " " & parse$(lsColumnComments, $WrdMrk, llDo2)
         next
      next

      slSel "Select * from sqlite_master where upper(tbl_name)='" & ucase$(slFN("tbl_name")) & "' and type='index'", 1
      do while slGetRow(1)

      loop

      ' Process indexes for this table
      llFlag = %False
      slSel "Select type,sql from sqlite_master where upper(tbl_name)='" & ucase$(slFN("tbl_name")) & "' and type='index'", 1
      do while slGetRow(1)
         if len(slFN("sql", 1)) then
            if isfalse llFlag then
               llFlag = %True
               print# glMapFileNumber, ""
               print# glMapFileNumber, space$(3) & "==========>>> Indexes"
            end if
            print# glMapFileNumber, space$(6) & slFN("sql", 1)
         end if
      loop

      ' Process triggers for this table
      llFlag = %False
      slSel "Select type,sql from sqlite_master where upper(tbl_name)='" & ucase$(slFN("tbl_name")) & "' and type='trigger'", 1
      do while slGetRow(1)
         if isfalse llFlag then
            llFlag = %True
            print# glMapFileNumber, ""
            print# glMapFileNumber, space$(3) & "==========>>> Triggers"
         end if
         print# glMapFileNumber, space$(6) & slFN("sql", 1)

      loop
      print# glMapFileNumber, ""

   loop

   ' Process views
   llFlag = 0
   slSel "Select name,tbl_name,sql from sqlite_master where type='view'
   do while slGetRow
      if isfalse llFlag then
         llFlag = %True
         print# glMapFileNumber, arBuildSectionHeader("Views")
      end if
      print# glMapFileNumber, space$(6) & slFN("sql")
   loop

   ' Exit OK
   function = %True

End Function