SQL Statement for listing tables

Community
1

Hi,
I am developing a database application using Sqlite3. There are a lot of tables in the database. What will be the SQL statements to retrieve all tables name in the database?

Regards
Bachok

2

Try Google - you‘ll find the answer easily. But here it is, just in case:

select name from sqlite_master where type='table'

sqlite_master is a system table that exists in all sqlite databases, which contains info about the objects stored in the database (tables, indexes, ….)
Just in case email corrupts the statement: the word table at the end must be enclosed in single apostrophes.
Thomas

3

Thanks Thomas.

This is my code:

Function Form_Student_Details_onshow()
 Dim sqlListing 
    sqlListing = []
    sqlListing[0] = ["SELECT name FROM sqlite_master WHERE type='table';",getTable,getTableError]
    Sql(DB,sqlListing)
  End Function
  
Function getTable(transaction,results)
  If results.rows.length > 0 Then
    MsgBox "Number of table are: " & results.rows.length
  Else
    MsgBox "No table found"
  End If

Using that code, I manage to get the number of tables available. How about getting the list of table names?

4

Have a look at results.rows in the Debugger, in the getTable function

console.log(results.rows)
5

Hi Bachok,
change Function getTable to look like this:

Function getTable(transaction,results)
  If results.rows.length>0 Then
    For i=0 to results.rows.length-1
      Print results.rows.item(i).name
    Next
  Else
    MsgBox "No table found"
  End If
End Function

This simply prints all table names into a console window. To get some better looking output, fill a grid (or some other window object) with the data. The table sqlite_master contains more info about the tables than just the names - do a “select * from sqlite_master …” and look at it in detail
Kind regards
Thomas

6

Thanks Tom,

So “results.rows.item(i).name” is the command for listing the table names.

Regards
Bachok