### List Database Tableinfo

To list the tableinformation for the tables of a database, read the content of the sqlite_master table.
Each record holds Type, name, tbl_name, rootpage, SQL.
Sub to get the Tableinformation from a SQLite Database
Region Project Attributes
...
'SQLite driver
#AdditionalJar: sqlite-jdbc-3.7.2
End Region
`Sub Process_Globals
Private gSQL As SQL
End Sub`
' Connect to the database file which is located in the dirapp folder
gSQL.InitializeSQLite(File.DirApp, "contacts.db", True)
`Sub Database_List_Tableinformation
Dim cursor As ResultSet
Dim cmd As String = "SELECT * FROM sqlite_master;"
Dim sb As StringBuilder
sb.Initialize
Try
cursor = gSQL.ExecQuery (cmd)
Do While cursor.NextRow
For i = 0 To cursor.ColumnCount - 1
sb.Append(cursor.GetColumnName(i) & " = " & cursor.GetString2(i) & CRLF)
Next
Loop
Catch
Log("ERROR: " & LastException.Message)
End Try
Log(sb.ToString)
End Sub`
Output Example from this Sub
Type = table
name = CONTACTS
tbl_name = CONTACTS
rootpage = 2
SQL = CREATE TABLE [CONTACTS] ([ID] INTEGER PRIMARY KEY, [Name] TEXT , [EMail] TEXT)
Type = table
name = CONTACTHISTORY
tbl_name = CONTACTHISTORY
rootpage = 3
SQL = CREATE TABLE [CONTACTHISTORY] ([ID] INTEGER PRIMARY KEY, [Name] INTEGER, [ContactDate] TEXT , [ContactNote] TEXT)
Sub to get the list of tables from a SQLite Database
This can be used to populate a Listview or a Combobox to select a table from.
'Get the list of tables for a database
`Sub Database_GetTables As List
Dim result As List
result.Initialize
Dim cursor As ResultSet
Dim cmd As String = "SELECT * FROM sqlite_master WHERE TYPE=""table"" ORDER BY name;"
Try
cursor = gSQL.ExecQuery (cmd)
Do While cursor.NextRow
result.Add(cursor.GetString("name"))
Loop
Catch
Log("ERROR: " & LastException.Message)
End Try
Return result
End Sub`

Changed: , Category: SQLite, File: T6C254M1830.htm, ID: 1830