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, May 30 '09 at 04:07

score 63 · Accepted Answer · answered May 30 '09 at 02:56

63

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 '09 at 02:56

4

Looks like that works on the command line--how can I achieve the same effect programatically? – Drew Hall May 30 '09 at 02:59
Thanks--this worked like a charm & is much simpler than what I was doing. Cheers! – Drew Hall May 30 '09 at 04:05
1

@Drew Hall This works also as a query executed programatically, not only from command line. – SWilk Mar 08 '10 at 10:04

score 10 · Answer 2 · answered May 30 '09 at 03:17

10

Execute the query:

PRAGMA table_info( your_table_name );

Documentation

answered May 30 '09 at 03:17

Nick Dandoulakis

42,588
16
104
136

score 5 · Answer 3 · answered Dec 08 '11 at 12:00

5

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;
});

answered Dec 08 '11 at 12:00

GeekTantra

11,580
6
41
55

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", ""]`. – legacybass Oct 16 '12 at 22:40
This is a major SQL "injection" risk. – Solomon Ucko Jul 21 '18 at 20:41

score 4 · Answer 4 · edited Sep 17 '13 at 20:19

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 '13 at 20:19

tanascius

53,078
22
114
136

answered Sep 13 '11 at 00:31

user941581

389
2
4

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` – conca Oct 25 '13 at 17:22
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!!! – Fletcher T. Penney Aug 18 '18 at 12:33

score 3 · Answer 5 · answered May 30 '09 at 03:09

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 · Answer 6 · 2014-06-19T18:08:12.710

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

Find SQLite Column Names in Empty Table

6 Answers6

Linked