Android Question Count of tables in SQL database.

Inuma

New Member
Licensed User
I have a sql database with an unknown count of tables. How to find the number of tables in the database?
For example I try:

B4X:
dim count_tables as Cursor
count_tables= Main.sql1.ExecQuery("SELECT COUNT() FROM sqlite_master WHERE TYPE='table'")
    count_tables.Position=0
    Msgbox("Tables",count_tables.GetInt(0))
but it returns error "column not exists"
 

Erel

Administrator
Staff member
Licensed User
Correct code:
B4X:
Dim count_tables As ResultSet = sql.ExecQuery("SELECT COUNT() FROM sqlite_master WHERE TYPE='table'")
count_tables.NextRow
Log("Number of tables: " & count_tables.GetInt2(0))
count_tables.Close
 

RB Smissaert

Well-Known Member
Licensed User
Correct code:
B4X:
Dim count_tables As ResultSet = sql.ExecQuery("SELECT COUNT() FROM sqlite_master WHERE TYPE='table'")
count_tables.NextRow
Log("Number of tables: " & count_tables.GetInt2(0))
count_tables.Close
This is a bit simpler:

B4X:
Sub CountTables
 Dim lCountTables As Long = SQL.ExecQuerySingleResult("select count(*) from sqlite_master where type = 'table'")
 Log("CountTables, lCountTables: " & lCountTables)
End Sub
RBS
 

eps

Well-Known Member
Licensed User
This is a bit simpler:

B4X:
Sub CountTables
 Dim lCountTables As Long = SQL.ExecQuerySingleResult("select count(*) from sqlite_master where type = 'table'")
 Log("CountTables, lCountTables: " & lCountTables)
End Sub
RBS
I would put the number in to an Int, but Long would more than suffice. There's no real need to put the asterisk in, I'd prefer a column_name in there, but it could throw an error if it no longer exists. It used to be accepted SQL practice, but then was frowned upon, but might work fine in this scenario but not in a 'normal' counting scenario, e.g. number of records in a specific table.
 
Top