How to retrieve the column names from the table?

Discussion in 'Code Samples & Tips' started by aeror, Jun 30, 2009.

  1. aeror

    aeror Member Licensed User

    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?
     
  2. agraham

    agraham Expert Licensed User

    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.
     
  3. RB Smissaert

    RB Smissaert Well-Known Member Licensed User

    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
     
  4. RB Smissaert

    RB Smissaert Well-Known Member Licensed User

    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
     
  5. RB Smissaert

    RB Smissaert Well-Known Member Licensed User

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


    RBS
     
  6. RB Smissaert

    RB Smissaert Well-Known Member Licensed User

    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
     
  7. aeror

    aeror Member Licensed User

    Thanks for the code!
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice