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 do

.headers ON 

you will get the desired result.

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 

Just for super noobs like me wondering how or what people meant by

PRAGMA table_info('table_name') 

You want to use use that as your prepare statement as shown below. Doing so selects a table that looks like this except is populated with values pertaining to your table.

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

Where id and name are the actual names of your columns. So to get that value you need to select column name by using:

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

Which will return the current row's column's name. To grab them all or find the one you want you need to iterate through all the rows. Simplest way to do so would be in the manner below.

//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 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; 

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

When you run the sqlite3 cli, typing in:

sqlite3 -header 

will also give the desired result

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

you can use Like statement if you are searching for any particular column

ex:

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

In order to get the column information you can use the following snippet:

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); } 

//JUST little bit modified the answer of giuseppe which returns 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; } 

.schema in sqlite console when you have you're inside the table it looks something like this for me ...

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

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)); }); } 

I know it's too late but this will help other.

To find the column name of the table, you should execute select * from tbl_name and you will get the result in sqlite3_stmt *. and check the column iterate over the total fetched column. Please refer following code for the same.

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

-(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; } 

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'; 

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; 

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!

I've not seen this elsewhere, but it seems to work for us: SELECT * FROM PRAGMA_table_list t JOIN PRAGMA_table_info(t.Name) c

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

If you want to know the specific field is in the table or not you can use this code:

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

Please fill yourTableNameand and yourFieldName.