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: stackoverflow.com/questions/928865/… — Drew Hall
– Drew Hall, Commented Aug 24, 2011 at 1:53

Wes · Accepted Answer · 2011-08-24 01:14:47Z

9

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, 2011 at 1:14

Wes

2,2241 gold badge21 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yam Marcovic Over a year ago

What if you don't have any records in that table?

Wes Over a year ago

I believe the column names are still available even if there are no results.

BenMorel · Accepted Answer · 2014-05-18 21:17:44Z

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, 2014 at 21:17

BenMorel

37k52 gold badges208 silver badges339 bronze badges

answered Aug 24, 2011 at 3:34

pabdulin

35.6k9 gold badges66 silver badges102 bronze badges

Comments

roymustang86 · Accepted Answer · 2011-08-24 01:48:34Z

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: "This is a legacy interface that is preserved for backwards compatibility. Use of this interface is not recommended."

Franke · Accepted Answer · 2019-01-07 01:51:09Z

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

.headers on

Collectives™ on Stack Overflow

Get column names in sqlite3

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Linked

Related