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

For the specific case of SQLite.swift , see this question and answer for a simple list of column names or this one for migration issues.

25 Answers 25

PRAGMA table_info(table_name); 

will get you a list of all the column names.

but you can't select from that table. It's just plain annoying. I'm trying something like this. but it don't work create temporary table TmpCols (cid integer, name text, type text, nn bit, dflt_value, pk bit); .mode insert TmpCols .output cols PRAGMA TABLE_INFO('yourtable'); .read cols .mode csv .output stdout

Just to put this into code terms for SQLiteDatabase on Android, write db.rawQuery("PRAGMA table_info(" + tablename + ")", null);

This will also work in case of View. PRAGMA table_info(View_Name); This will list all columns of a View

To execute as a query, see answer from @user1461607: select * from pragma_table_info('tblName') as tblInfo;

.headers ON 

you will get the desired result.

For some reasons I don't know, the PRAGMA method and the .schema method both didn't work for me. But this one works just fine.

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 

Although the output isn't as "readable" (perhaps) this is_much_ easier to remember than PRAGMA table_info(table_name);

@NickTomlin Unfortunately, this method requires having the sqlite3 command line program, as dot commands are not valid SQL.

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

What they meant by that is to execute sqlite3 (or whatever it is named for you) to go into the sqlite CLI and then type in that text. No need to write extensive code for that :)

Yes, as @Xerus says. no need for extensive code. Just use sqlite3 directly. Also, @birdbuster, it helps to specify the language and library you are using. It looks to me like C++ (from the sprintf function). It is helpful to clarify, since the OP question was language-agnostic.

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

@JinghuiNiu It works if you pass the schema as the second argument, pragma_table_info('table', 'schema') .

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; 

The answer could need an explanation: After all it should be an answer, and not a "guess what this code does" riddle.

When you run the sqlite3 cli, typing in:

sqlite3 -header 

will also give the desired result

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