SQLite Forum

pragma_table_info virtual table, same table names different schemas
Login

pragma_table_info virtual table, same table names different schemas

(1) By spankyhoot on 2020-10-05 00:29:45 [link] [source]

Hi,

I'm expecting the second output to be:

0|c2|text|0||0

is there a way to achieve what I'm trying to do?

% ./sqlite3
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> ATTACH DATABASE ':memory:' AS db1;
sqlite> ATTACH DATABASE ':memory:' AS db2;
sqlite> CREATE TABLE db1.x (c1 text);
sqlite> CREATE TABLE db2.x (c2 text);
sqlite> select * from db1.pragma_table_info('x');
0|c1|text|0||0
sqlite> select * from db2.pragma_table_info('x');
0|c1|text|0||0
sqlite>

(2) By Richard Hipp (drh) on 2020-10-05 11:32:50 in reply to 1 [source]

Correct syntax is:

   SELECT * FROM pragma_table_info('x','db2');

The built-in table-valued functions do not belong to any particular datatabase schema. So the "db2." prefix does not do anything. You have to specify the schema that you want to query as a second parameter.

(3) By spankyhoot on 2020-10-06 02:16:00 in reply to 2 [link] [source]

thanks!