pragma table_info confusion by multiple attache databases
(1) By anonymous on 2022-01-18 21:47:23 [link] [source]
When different databases attached to a (memmory) database ( for example store and arch, with both a table TEST), there is a confusion when asking table information :
few example's in my case :
a:select * from arch.pragma_table_info('TEST') is wrong
b:select * from store.pragma_table_info('TEST') is ok
while
c:pragma arch.table_info('TEST') Ok
d:pragma store.table_info('TEST') Ok
and
select * from pragma_table_info('TEST') is not clear but an answer b
pragma table_info('TEST') the same answer b
(2) By Larry Brasfield (larrybr) on 2022-01-19 00:22:54 in reply to 1 [link] [source]
Try:
select * from pragma_table_info('TEST', 'arch')
and
select * from pragma_table_info('TEST', 'store')
.
This has been less than obvious in the docs, but that is about to change.
(3) By Ryan Smith (cuz) on 2022-01-19 00:29:32 in reply to 1 [source]
The function does not have a schema, or put differently, all functions can be called from all schemas. It's only the table that has a schema.
i.e. try:
a:select * from pragma_table_info('TEST', 'arch');
etc.
Which should make it obvious, and to see what is meant with "all functions are available in all schemas", note that:
select * from pragma_table_info('TEST', 'arch');
select * from arch.pragma_table_info('TEST', 'arch');
select * from store.pragma_table_info('TEST', 'arch');
are all 100% equivalent in meaning.
(4.1) By Keith Medcalf (kmedcalf) on 2022-01-19 01:22:50 edited from 4.0 in reply to 1 [link] [source]
If you are going to use the virtual table syntax, then you can do so:
select * from pragma_table_info('test') where schema == 'store';
select * from pragma_table_info where arg == 'test' and schema == 'arch';
Note that if you do pragma table_info('pragma_table_info') you will be presented with information regarding the pragma_table_info table.
sqlite> pragma table_xinfo('pragma_table_xinfo');
┌─────┬────────────┬──────┬─────────┬──────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │ name │ type │ aff │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼────────────┼──────┼─────────┼──────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ -1 │ │ │ INTEGER │ │ 0 │ │ 1 │ 1 │ 0 │ 1 │
│ 0 │ cid │ │ BLOB │ │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 1 │ name │ │ BLOB │ │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 2 │ type │ │ BLOB │ │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 3 │ aff │ │ BLOB │ │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 4 │ coll │ │ BLOB │ │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 5 │ notnull │ │ BLOB │ │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 6 │ dflt_value │ │ BLOB │ │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 7 │ pk │ │ BLOB │ │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 8 │ rowid │ │ BLOB │ │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 9 │ autoinc │ │ BLOB │ │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 10 │ hidden │ │ BLOB │ │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 11 │ arg │ │ NUMERIC │ │ 0 │ │ 0 │ 0 │ 0 │ 1 │
│ 12 │ schema │ │ NUMERIC │ │ 0 │ │ 0 │ 0 │ 0 │ 1 │
└─────┴────────────┴──────┴─────────┴──────┴─────────┴────────────┴────┴───────┴─────────┴────────┘
NOTE: My implementation of the table_[x]info pragma returns additional information from the internal data dictionary that is not displayed in the as-distributed pragma.
NOTE ALSO: You have to use table_xinfo to get information on hidden columns.