Get column names in sqlite3

Question

I would like to print my sql table contents and for that reason, I would like to retrieve the column name from the table. One solution I came across was :

SELECT sql FROM sqlite_master WHERE tbl_name = 'table_name' AND type = 'table'

But looks like I will have to parse the results.

Another suggestion was to use:

PRAGMA table_info(table_name);

but the below sqlite page suggests not to use this : http://www.sqlite.org/pragma.html#pragma_full_column_names

Does there exists any way to achieve this. Also what would be the syntax to use

PRAGMA table_info(table_name);

Above solutions have been taken from here

• Drew Hall: I asked a similar question a while ago. See the answers there: stackoverflow.com/questions/928865/…

Answer 1

Since your question is tagged c I assume you have access to the SQLite C API. If you create a prepared statement with one of the prepare_v2 functions that selects from the table you want you can use sqlite3_column_name to get the name of each column.

• Yam Marcovic: What if you don't have any records in that table?
• Wes: I believe the column names are still available even if there are no results.

Answer 2

You can safely use PRAGMA table_info(table-name); since it's not deprecated in any way (yours post links to another pragma).

Answer 3

int sqlite3_get_table(
 sqlite3 *db, /* An open database */
 const char *zSql, /* SQL to be evaluated */
 char ***pazResult, /* Results of the query */
 int *pnRow, /* Number of result rows written here */
 int *pnColumn, /* Number of result columns written here */
 char **pzErrmsg /* Error msg written here */
 );

If you are using c/c++, you can use the function sqlite3_get_table(db, query, result, nrow, ncol, errmsg);

Make the query as select * from table;

And the first few results result[0], result[1]...... will have the column names.

• mu is too short: That function is deprecated: "This is a legacy interface that is preserved for backwards compatibility. Use of this interface is not recommended."

Answer 4

This setting will toggle showing column names as part of the return for select statements:

.headers on