How to get a list of column names on Sqlite3 database?

PRAGMA table_info(table_name);

will get you a list of all the column names.

If you have the sqlite database, use the sqlite3 command line program and these commands:

To list all the tables in the database:

.tables

To show the schema for a given tablename:

.schema tablename

If you want the output of your queries to include columns names and be correctly aligned as columns, use these commands in sqlite3:

.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

An alternative way to get a list of column names not mentioned here that is cross platform and does not rely on the sqlite3.exe shell is to select from the PRAGMA_TABLE_INFO() table value function.

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

You can check if a certain column exists by querying:

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.

Note this feature is experimental and was added in SQLite version 3.16.0 (2017-01-02).

Reference:

https://www.sqlite.org/pragma.html#pragfunc

To get a list of columns you can simply use:

.schema tablename

I know it is an old thread, but recently I needed the same and found a neat way:

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

.schema table_name

This will list down the column names of the table from the database.

Hope this will help!!!

This is an old question, but here is an alternative answer that retrieves all the columns in the SQLite database, with the name of the associated table for each column :

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.

This command below sets column names:

.header on

Then, this is how it looks like 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 unsets column names:

.header off

Then, this is how it looks like below:

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

And these commands show the details of the command ".header":

.help .header

Or:

.help header

Then, this is how it looks like below:

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

In addition, this command below sets the output mode "box":

.mode box

Then, this is how it looks like 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, this is how it looks like 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 show the details of the command ".mode":

.help .mode

Or:

.help mode

Then, this is how it looks like below:

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

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;

     //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 the 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 like the following

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])

I was able to retrieve table names with corresponding columns by using one sql query, but columns output is comma separated. I hope it helps somebody

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

For use in Python with sqlite3

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

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

When using sqlite3 in Python, simply 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"]

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