Find SQLite Column Names in Empty Table

Question

For kicks I'm writing a "schema documentation" tool that generates a description of the tables and relationships in a database. I'm currently shimming it to work with SQLite.

I've managed to extract the names of all the tables in a SQLite database via a query on the sqlite_master table. For each table name, I then fire off a simple

select * from <table name>

query, then use the sqlite3_column_count() and sqlite3_column_name() APIs to collect the column names, which I further feed to sqlite3_table_column_metadata() to get additional info. Simple enough, right?

The problem is that it only works for tables that are not empty. That is, the sqlite_column_*() APIs are only valid if sqlite_step() has returned SQLITE_ROW, which is not the case for empty tables.

So the question is, how can I discover column names for empty tables? Or, more generally, is there a better way to get this type of schema info in SQLite?

I feel like there must be another hidden sqlite_xxx table lurking somewhere containing this info, but so far have not been able to find it.

Thanks to all. I mistakenly thought the PRAGMA interface was only for the sqlite3 command line client. — Drew Hall
– Drew Hall, Commented May 30, 2009 at 4:07

pragmanatu · Accepted Answer · 2009-05-30 02:56:24Z

64

sqlite> .header on sqlite> .mode column sqlite> create table ABC(A TEXT, B VARCHAR); sqlite> pragma table_info(ABC); cid name type notnull dflt_value pk ---------- ---------- ---------- ---------- ---------- ---------- 0 A TEXT 0 0 1 B VARCHAR 0 0

answered May 30, 2009 at 2:56

pragmanatu

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

3 Comments

Drew Hall Over a year ago

Looks like that works on the command line--how can I achieve the same effect programatically?

Drew Hall Over a year ago

Thanks--this worked like a charm & is much simpler than what I was doing. Cheers!

SWilk Over a year ago

@Drew Hall This works also as a query executed programatically, not only from command line.

Nick Dandoulakis · Accepted Answer · 2009-05-30 03:17:33Z

10

Execute the query:

PRAGMA table_info( your_table_name );

Documentation

answered May 30, 2009 at 3:17

Nick Dandoulakis

43.3k17 gold badges106 silver badges139 bronze badges

Comments

Dev Sahoo · Accepted Answer · 2011-12-08 12:00:52Z

PRAGMA table_info( your_table_name ); doesn't work in HTML5 SQLite.

Here is a small HTML5 SQLite JavaScript Snippet which gets the column names from your_table_name even if its empty. Hope its helpful.

tx.executeSql('SELECT name, sql FROM sqlite_master WHERE type="table" AND name = "your_table_name";', [], function (tx, results) { var columnParts = results.rows.item(0).sql.replace(/^[^\(]+\(([^\)]+)\)/g, '$1').split(','); var columnNames = []; for(i in columnParts) { if(typeof columnParts[i] === 'string') columnNames.push(columnParts[i].split(" ")[0]); } console.log(columnNames); ///// Your code which uses the columnNames; });

It looks like if your table has more than one column, columns 2 - n don't get processed because of leading whitespace. This can be fixed by calling the trim() function on the string (or implementing it yourself for browsers that don't yet support trim()). Calling the above function on a table (Person) with an ID and Name field returns ["id", ""].

tanascius · Accepted Answer · 2013-09-17 20:19:01Z

4

Execute this query

select * from (select "") left join my_table_to_test b on -1 = b.rowid;

You can try it at online sqlite engine

edited Sep 17, 2013 at 20:19

tanascius

54.1k24 gold badges117 silver badges139 bronze badges

answered Sep 13, 2011 at 0:31

user941581

3992 silver badges6 bronze badges

2 Comments

conca Over a year ago

this way is the best! If you dont know the name of the rowid column I suggest using: SELECT t.* FROM (SELECT 1) LEFT JOIN table AS t LIMIT 1

Fletcher T. Penney Over a year ago

The suggestion by @conca led me to the solution I needed, which was to union the normal query with the "backup query for empty tables". SELECT * from 'table' UNION SELECT t.* FROM (SELECT 1) LEFT JOIN 'table' AS t;" This allows a single query to work whether the table has data or not, and I can access the column names. Thanks a lot!!!

Alex Martelli · Accepted Answer · 2009-05-30 03:09:54Z

The PRAGMA statement suggested by @pragmanatu works fine through any programmatic interface, too. Alternatively, the sql column of sqlite_master has the SQL statement CREATE TABLE &c &c that describes the table (but, you'd have to parse that, so I think PRAGMA table_info is more... pragmatic;-).

score 0 · Accepted Answer · 2014-06-19 18:08:12Z

If you are suing SQLite 3.8.3 or later (supports the WITH clause), this recursive query should work for basic tables. On CTAS, YMMV.

WITH Recordify(tbl_name, Ordinal, Clause, Sql) AS ( SELECT tbl_name, 0, '', Sql FROM ( SELECT tbl_name, substr ( Sql, instr(Sql, '(') + 1, length(Sql) - instr(Sql, '(') - 1 ) || ',' Sql FROM sqlite_master WHERE type = 'table' ) UNION ALL SELECT tbl_name, Ordinal + 1, trim(substr(Sql, 1, instr(Sql, ',') - 1)), substr(Sql, instr(Sql, ',') + 1) FROM Recordify WHERE Sql > '' AND lower(trim(Sql)) NOT LIKE 'check%' AND lower(trim(Sql)) NOT LIKE 'unique%' AND lower(trim(Sql)) NOT LIKE 'primary%' AND lower(trim(Sql)) NOT LIKE 'foreign%' AND lower(trim(Sql)) NOT LIKE 'constraint%' ), -- Added to make querying a subset easier. Listing(tbl_name, Ordinal, Name, Constraints) AS ( SELECT tbl_name, Ordinal, substr(Clause, 1, instr(Clause, ' ') - 1), trim(substr(Clause, instr(Clause, ' ') + 1)) FROM Recordify WHERE Ordinal > 0 ) SELECT tbl_name, Ordinal, Name, Constraints FROM Listing ORDER BY tbl_name, lower(Name);

Collectives™ on Stack Overflow

Find SQLite Column Names in Empty Table

6 Answers 6

3 Comments

Comments

2 Comments

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

Comments

2 Comments

2 Comments

Comments

Comments

Linked

Related