How to retrieve the column names from the table?

[aeror]

Hi

I searched the forum but could not find the answer:
how to get the column names from the SQLite table.
The only information I found in the web was that the structure of the table is returned with PRAGMA TABLE_INFO() command. But it seems that PRAGMA returns different object than the standard SELECT.
Anyone knows how to do that?

 

[agraham]

Others know better than I but if you query the master table

"select * from sqlite_master"

It should return the SQL statement used to create each table in the database from which you can see the column names.

 

[RB Smissaert]

You have to run a query like this:

select
sql
from
sqlite_master
where
name = 'table1'

and then parse out the fields from the obtained create table sql.


RBS

 

[RB Smissaert]

Actually, it is simpler to use a pragma to get the table fields:

PRAGMA table_info ('Table1')

As it will save you parsing out the fields from the create sql.
It will give you the table meta-date like this:

cid name type notnull dflt_value pk
------------------------------------------------
0 FIELD1 INTEGER 0 1
1 FIELD2 TEXT 0 0
2 FIELD3 TEXT 0 0
3 FIELD4 REAL 0 0


RBS

 

[RB Smissaert]

table meta-date

To avoid any confusion, this should be:
table meta-data


RBS

 

[RB Smissaert]

B4P code would be something like this, where Hashtable1 is the new
hashtable control:

Sub GetTableFieldsAndDataTypes(strTable)

cmd.CommandText = "PRAGMA table_info ('" & strTable & "')"
Reader.Value = cmd.ExecuteReader
Hashtable1.Clear

Do While Reader.ReadNextRow = True
'field name to Key and field data type to Value
'----------------------------------------------
Hashtable1.Add(Reader.GetValue(1), Reader.GetValue(2))
Loop

Reader.Close

End Sub


RBS

 

[aeror]

Thanks for the code!