Hi,

I need to access the list of column names and types like:

select name, type from pragma_table_info('tbl');

But I have two tables named tbl:

a.tbl (from an attached database) tbl

It seems I cannot specify a schema? I've tried both but neither work:

select name, type from pragma_table_info('a.tbl');
select name, type from a.pragma_table_info('tbl');

Of course I can do this, but then I need to write custom software to post process the results:

pragma a.table_info('tbl');

Might there be a way to support table schema names using the select ... approach?

I can't help you, but I feel that if this doesn't work

    select name, type from pragma_table_info('a.tbl');

it's a bug. I hope one of the devs sees your post.

This is what the docs say:

PRAGMA functions
[...]
The PRAGMA argument and schema, if any, are passed as arguments to the table-valued function, with the schema as an optional, last argument.

Did you try that function format?

Why not use pragma table_xinfo('pragma_table_info') to find out what the columns of the pragma_table_info virtual table are? When I execute it. I get the following output (yours will be different).

sqlite> pragma table_xinfo('pragma_table_info');
┌─────┬──────────────┬──────┬───────────┬──────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │     name     │ type │    aff    │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼──────────────┼──────┼───────────┼──────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ -1  │ NULL         │ NULL │ 'INTEGER' │ NULL │ 0       │ NULL       │ 1  │ 1     │ 0       │ 1      │
│ 0   │ 'cid'        │ NULL │ 'BLOB'    │ NULL │ 0       │ NULL       │ 0  │ 0     │ 0       │ 0      │
│ 1   │ 'name'       │ NULL │ 'BLOB'    │ NULL │ 0       │ NULL       │ 0  │ 0     │ 0       │ 0      │
│ 2   │ 'type'       │ NULL │ 'BLOB'    │ NULL │ 0       │ NULL       │ 0  │ 0     │ 0       │ 0      │
│ 3   │ 'aff'        │ NULL │ 'BLOB'    │ NULL │ 0       │ NULL       │ 0  │ 0     │ 0       │ 0      │
│ 4   │ 'coll'       │ NULL │ 'BLOB'    │ NULL │ 0       │ NULL       │ 0  │ 0     │ 0       │ 0      │
│ 5   │ 'notnull'    │ NULL │ 'BLOB'    │ NULL │ 0       │ NULL       │ 0  │ 0     │ 0       │ 0      │
│ 6   │ 'dflt_value' │ NULL │ 'BLOB'    │ NULL │ 0       │ NULL       │ 0  │ 0     │ 0       │ 0      │
│ 7   │ 'pk'         │ NULL │ 'BLOB'    │ NULL │ 0       │ NULL       │ 0  │ 0     │ 0       │ 0      │
│ 8   │ 'rowid'      │ NULL │ 'BLOB'    │ NULL │ 0       │ NULL       │ 0  │ 0     │ 0       │ 0      │
│ 9   │ 'autoinc'    │ NULL │ 'BLOB'    │ NULL │ 0       │ NULL       │ 0  │ 0     │ 0       │ 0      │
│ 10  │ 'arg'        │ ''   │ 'NUMERIC' │ NULL │ 0       │ NULL       │ 0  │ 0     │ 0       │ 1      │
│ 11  │ 'schema'     │ ''   │ 'NUMERIC' │ NULL │ 0       │ NULL       │ 0  │ 0     │ 0       │ 1      │
└─────┴──────────────┴──────┴───────────┴──────┴─────────┴────────────┴────┴───────┴─────────┴────────┘

You will note that the output columns are described together with two HIDDEN columns called arg and schema. These are the input parameters to the virtual table.

arg is the argument of the pragma function, in this case the table name. schema is the schema in which to look for the table name.

select * from pragma_table_info where arg='table' and schema='schema'

Thanks all, I wrongly assumed schema would come as first arg. My bad! also thanks for Xinfo idea!