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

I asked a similar question a while ago. See the answers there: http://stackoverflow.com/questions/928865/find-sqlite-column-names-in-empty-table — Drew Hall, Aug 24 '11 at 01:53

score 8 · Answer 1 · answered Aug 24 '11 at 01:14

8

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.

answered Aug 24 '11 at 01:14

Wes

2,036
1
24
20

1

What if you don't have any records in that table? – Yam Marcovic May 09 '13 at 16:21
1

I believe the column names are still available even if there are no results. – Wes May 10 '13 at 12:20

score 4 · Answer 2 · edited May 18 '14 at 21:17

4

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

edited May 18 '14 at 21:17

BenMorel

31,815
47
169
296

answered Aug 24 '11 at 03:34

Petr Abdulin

32,124
8
59
93

score 1 · Answer 3 · answered Aug 24 '11 at 01:48

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.

That function is [deprecated](http://www.sqlite.org/c3ref/free_table.html): "This is a legacy interface that is preserved for backwards compatibility. Use of this interface is not recommended." — mu is too short, Aug 24 '11 at 02:11

score 0 · Answer 4 · answered Jan 07 '19 at 01:51

0

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

.headers on

answered Jan 07 '19 at 01:51

Franke

1,147
11
14

Get column names in sqlite3

4 Answers4