How do I get a list of table column names from an SQLite database? [closed]

PRAGMA table_info(table_name);

will get you a list of all the column names.

.headers ON will get the desired result.

Use the SQLite command line program.

To list all the tables in the database:

.tables

To show the schema for a given tablename:

.schema tablename

PRAGMA table_info('table_name')

Use that as prepared statement. It selects a table that is populated with values pertaining to your table:

cid         name        type        notnull     dflt_value  pk
----------  ----------  ----------  ----------  ----------  ----------
0           id          integer     99                      1
1           name                    0                       0

id and name are the actual names of your columns. To get that value you need to select a column name:

//returns the name
sqlite3_column_text(stmt, 1);
//returns the type
sqlite3_column_text(stmt, 2);

Which will return the current row's column name. You need to iterate through all the rows:

//where rc is an int variable if wondering :/
rc = sqlite3_prepare_v2(dbPointer, "pragma table_info ('your table name goes here')", -1, &stmt, NULL);

if (rc==SQLITE_OK)
{
    //will continue to go down the rows (columns in your table) till there are no more
    while(sqlite3_step(stmt) == SQLITE_ROW)
    {
        sprintf(colName, "%s", sqlite3_column_text(stmt, 1));
        //do something with colName because it contains the column's name
    }
}

If you want the output to include column names aligned as columns, use these commands in SQLite's CLI:

.headers on
.mode column

You will get output like:

sqlite> .headers on
sqlite> .mode column
sqlite> select * from mytable;
id          foo         bar
----------  ----------  ----------
1           val1        val2
2           val3        val4

To get a list of column names without the sqlite3.exe shell, select from the PRAGMA_TABLE_INFO() function:

SELECT name FROM PRAGMA_TABLE_INFO('your_table');
name
tbl_name
rootpage
sql

You can check if a certain column exists by:

SELECT 1 FROM PRAGMA_TABLE_INFO('your_table') WHERE name='column1';
1

This is what you use if you don't want to parse the result of select sql from sqlite_master or pragma table_info. This feature was added in SQLite version 3.16.0 (2017-01-02).

To get a list of columns you can use .schema tablename.

These commands below can set column names:

.headers on

.header on

Then, you can get the result with column names as shown below:

sqlite> SELECT * FROM user;
id|first_name|last_name|age
1|Steve|Jobs|56
2|Bill|Gates|66
3|Mark|Zuckerberg|38

And, these commands below can unset column names:

.headers off

.header off

Then, you can get the result without column names as shown below:

sqlite> SELECT * FROM user;
1|Steve|Jobs|56
2|Bill|Gates|66
3|Mark|Zuckerberg|38

And, these commands below can show the details of the command .headers:

.help .headers

.help .header

.help headers

.help header

Then, you can show the details of the command .headers as shown below:

sqlite> .help .headers
.headers on|off          Turn display of headers on or off

In addition, this command below can set the output mode box:

.mode box

Then, you can set the output mode box as shown below:

sqlite> SELECT * FROM user;
┌────┬────────────┬────────────┬─────┐
│ id │ first_name │ last_name  │ age │
├────┼────────────┼────────────┼─────┤
│ 1  │ Steve      │ Jobs       │ 56  │
│ 2  │ Bill       │ Gates      │ 66  │
│ 3  │ Mark       │ Zuckerberg │ 38  │
└────┴────────────┴────────────┴─────┘

And, this command below sets the output mode table:

.mode table

Then, you can set the output mode table as shown below:

sqlite> SELECT * FROM user;
+----+------------+------------+-----+
| id | first_name | last_name  | age |
+----+------------+------------+-----+
| 1  | Steve      | Jobs       | 56  |
| 2  | Bill       | Gates      | 66  |
| 3  | Mark       | Zuckerberg | 38  |
+----+------------+------------+-----+

And, these commands can show the details of the command .mode:

.help .mode

.help mode

.help modes

Then, you can show the details of the command .mode:

sqlite> .help .mode
.import FILE TABLE       Import data from FILE into TABLE
   Options:
     --ascii               Use \037 and \036 as column and row separators
     --csv                 Use , and \n as column and row separators
     --skip N              Skip the first N rows of input
     --schema S            Target table to be S.TABLE
     -v                    "Verbose" - increase auxiliary output
   Notes:
     *  If TABLE does not exist, it is created.  The first row of input
        determines the column names.
     *  If neither --csv or --ascii are used, the input mode is derived
        from the ".mode" output mode
     *  If FILE begins with "|" then it is a command that generates the
        input text.
.mode MODE ?OPTIONS?     Set output mode
   MODE is one of:
     ascii       Columns/rows delimited by 0x1F and 0x1E
     box         Tables using unicode box-drawing characters
     csv         Comma-separated values
     column      Output in columns.  (See .width)
     html        HTML <table> code
     insert      SQL insert statements for TABLE
     json        Results in a JSON array
     line        One value per line
     list        Values delimited by "|"
     markdown    Markdown table format
     qbox        Shorthand for "box --width 60 --quote"
     quote       Escape answers as for SQL
     table       ASCII-art table
     tabs        Tab-separated values
     tcl         TCL list elements
   OPTIONS: (for columnar modes or insert mode):
     --wrap N       Wrap output lines to no longer than N characters
     --wordwrap B   Wrap or not at word boundaries per B (on/off)
     --ww           Shorthand for "--wordwrap 1"
     --quote        Quote output text as SQL literals
     --noquote      Do not quote output text
     TABLE          The name of SQL table used for "insert" mode

Lastly, you can show the commands .headers and .mode with .help as shown below:

sqlite> .help     
...
.headers on|off          Turn display of headers on or off
...
.mode MODE ?OPTIONS?     Set output mode
...

I found a neat way:

SELECT c.name FROM pragma_table_info('your_table_name') c;

From the SQLite CLI, sqlite3 -header will also give the desired result.

.schema table_name will list column names of the table from the database.

An alternative that retrieves all columns in the SQLite database with the name of the associated table:

WITH tables AS (SELECT name tableName, sql 
FROM sqlite_master WHERE type = 'table' AND tableName NOT LIKE 'sqlite_%')
SELECT fields.name, fields.type, tableName
FROM tables CROSS JOIN pragma_table_info(tables.tableName) fields

This returns this type of result:

{
    "name": "id",
    "type": "integer",
    "tableName": "examples"
}, {
    "name": "content",
    "type": "text",
    "tableName": "examples"
}

for a simple table containing an identifier and a string content.

Use a LIKE statement if you are searching for any particular column:

SELECT * FROM sqlite_master where sql like('%LAST%')

To get the column information:

String sql = "select * from "+oTablename+" LIMIT 0";
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(sql);
ResultSetMetaData mrs = rs.getMetaData();
for(int i = 1; i <= mrs.getColumnCount(); i++)
{
    Object row[] = new Object[3];
    row[0] = mrs.getColumnLabel(i);
    row[1] = mrs.getColumnTypeName(i);
    row[2] = mrs.getPrecision(i);
}

.schema in SQLite's console:

sqlite>.schema
CREATE TABLE players(
id integer primary key,
Name varchar(255),
Number INT,
Team varchar(255)

I modified this answer which returns an array of table columns:

+(NSMutableArray*)tableInfo:(NSString *)table{
    sqlite3_stmt *sqlStatement;
    NSMutableArray *result = [NSMutableArray array];
    const char *sql = [[NSString stringWithFormat:@"PRAGMA table_info('%@')",table] UTF8String];

    if(sqlite3_prepare(md.database, sql, -1, &sqlStatement, NULL) != SQLITE_OK){
        NSLog(@"Problem with prepare statement tableInfo %@",
                [NSString stringWithUTF8String:(const char *)sqlite3_errmsg(md.database)]);
    }

    while (sqlite3_step(sqlStatement)==SQLITE_ROW){
        [result addObject:
          [NSString stringWithUTF8String:(char*)sqlite3_column_text(sqlStatement, 1)]];
    }

    return result;
}

To find the column names of the table, execute select * from tbl_name and you will get the result in sqlite3_stmt *. Iterate the fetched columns:

// sqlite3_stmt *statement ;
int totalColumn = sqlite3_column_count(statement);
for (int iterator = 0; iterator<totalColumn; iterator++) {
   NSLog(@"%s", sqlite3_column_name(statement, iterator));
}

This will print all the column names of the result set.

function getDetails(){
    var data = [];
    dBase.executeSql("PRAGMA table_info('table_name') ", [], function(rsp){
        if(rsp.rows.length > 0){
            for(var i=0; i<rsp.rows.length; i++){
                var o = {
                    name: rsp.rows.item(i).name,
                    type: rsp.rows.item(i).type
                }
                data.push(o);
            }
        }
        alert(rsp.rows.item(0).name);

    },function(error){
        alert(JSON.stringify(error));
    });
}

Get a list of tables and columns as a view:

CREATE VIEW Table_Columns AS
SELECT m.tbl_name AS TableView_Name, m.type AS TableView, cid+1 AS Column, p.*
FROM sqlite_master m, Pragma_Table_Info(m.tbl_name) p
WHERE m.type IN ('table', 'view') AND
   ( m.tbl_name = 'mypeople' OR m.tbl_name LIKE 'US_%')   -- filter tables
ORDER BY m.tbl_name;

I retrieve table names with corresponding columns by one SQL query, but output is comma separated:

SELECT
    tbl_name,
    (SELECT GROUP_CONCAT(name, ',') FROM PRAGMA_TABLE_INFO(tbl_name)) AS columns
FROM sqlite_schema
WHERE type = 'table';

-(NSMutableDictionary*)tableInfo:(NSString *)table{
  sqlite3_stmt *sqlStatement;
  NSMutableDictionary *result = [[NSMutableDictionary alloc] init];
  const char *sql = [[NSString stringWithFormat:@"pragma table_info('%s')",[table UTF8String]] UTF8String];
  if(sqlite3_prepare(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK){
    NSLog(@"Problem with prepare statement tableInfo %@",[NSString stringWithUTF8String:(const char *)sqlite3_errmsg(db)]);
  }
  while (sqlite3_step(sqlStatement)==SQLITE_ROW){
    [result setObject:@"" forKey:[NSString stringWithUTF8String:(char*)sqlite3_column_text(sqlStatement, 1)]];
  }
  return result;
}

It seems to work:

SELECT * FROM PRAGMA_table_list t JOIN PRAGMA_table_info(t.Name) c

For use in Python with sqlite3

This answer PRAGMA table_info() returns a list of tuples, which might not be suitable for further processing:

[(0, 'id', 'INTEGER', 0, None, 0),
 (1, 'name', 'TEXT', 0, None, 0),
 (2, 'age', 'INTEGER', 0, None, 0),
 (3, 'profession', 'TEXT', 0, None, 0)]

Add a list comprehension in the end to filter out unwanted information:

import sqlite3 as sq

def col_names(t_name):
    with sq.connect('file:{}.sqlite?mode=ro'.format(t_name),uri=True) as conn:
        cursor = conn.cursor()
        cursor.execute("PRAGMA table_info({}) ".format(t_name))
        data = cursor.fetchall()
        return [i[1] for i in data]

col_names("your_table_name")

Result:

["id","name","age","profession"]

Do not use in production as this snippet is subject to SQL injection!

//Called when application is started. It works on Droidscript, it is tested:
function OnStart()
{
//Create a layout with objects vertically centered.
lay = app.CreateLayout( "linear", "VCenter,FillXY" );

//Create a text label and add it to layout.
txt = app.CreateText( "", 0.9, 0.4, "multiline" )
lay.AddChild( txt );
app.AddLayout(lay);

db = app.OpenDatabase( "MyData" )

//Create a table (if it does not exist already).
db.ExecuteSql( "drop table if exists test_table" )
db.ExecuteSql( "CREATE TABLE IF NOT EXISTS test_table " +
   "(id integer primary key, data text, num integer)",[],null, OnError )
   db.ExecuteSql( "insert into test_table values (1,'data10',100),
   (2,'data20',200),(3,'data30',300)")
   //Get all the table rows.
   DisplayAllRows("SELECT * FROM test_table");
   DisplayAllRows("select *, id+100 as idplus, 'hahaha' as blabla from
   test_table order by id desc;")
}

//function to display all records
function DisplayAllRows(sqlstring)  // <-- can you use for any table not need to
                                //  know column names, just use a *
                                // example:
{
//Use all rows what is in ExecuteSql  (try any, it will works fine)
db.ExecuteSql( sqlstring, [], OnResult, OnError )
}
//Callback to show query results in debug.
function OnResult( res )
{
var len = res.rows.length;
var s = txt.GetText();
// ***********************************************************************
// This is the answer how to read column names from table:
for(var ColumnNames in res.rows.item(0)) s += " [ "+ ColumnNames +" ] "; // "[" & "]" optional, i use only in this demo
// ***********************************************************************
//app.Alert("Here is all Column names what Select from your table:\n"+s);
s+="\n";
for(var i = 0; i < len; i++ )
{
    var rows = res.rows.item(i)
    for (var item in rows)
        {
            s += "    " + rows[item] + "   ";
        }
    s+="\n\n";
}
//app.Alert(s);
txt.SetText( s )
}
//Callback to show errors.
function OnError( msg )
{
   app.Alert( "Error: " + msg )
}

If you're using SQLite3, INFORMATION_SCHEMA is not supported. Use PRAGMA table_info instead. This will return 6 rows of information about the table. To fetch the column name (row2), use a for loop:

cur.execute("PRAGMA table_info(table_name)")  # fetches the 6 rows of data
records = cur.fetchall() 
print(records)
for row in records:
    print("Columns: ", row[1])

If you want to know if a specific column is in a table you can use:

select * from pragma_table_info('yourTableName') as tblInfo
where name='yourFieldName'

SELECT
    d.seq AS DB_SEQ,
    d.file AS TABLE_CATALOG,
    t.schema AS TABLE_SCHEMA,
    t.type AS  TABLE_TYPE,
    t.name AS TABLE_NAME,
    p.cid + 1  AS ORDINAL_POSITION,
    p.name AS COLUMN_NAME,
    CASE p."notnull"
        WHEN 1 THEN 'NO'
        ELSE 'YES'
    END AS IS_NULLABLE,
    p.dflt_value AS COLUMN_DEFAULT,
    p.type AS DATA_TYPE,
    p.hidden AS COLUMN_IS_HIDDEN,
    p.pk AS IS_COLUMN_IN_PK,
    p."notnull" AS IS_NOT_NULLABLE,
    p.cid
FROM pragma_table_list AS t
CROSS JOIN pragma_table_xinfo(t.name, t.schema) AS p
LEFT JOIN pragma_database_list AS d ON d.name = t.schema
ORDER BY t.name, p.cid