Along with the table_info pragma you could also look at the output of an explain statement to see how it gets the value. When it's a rowid table it uses a "Rowid" opcode, vs using a "Column" opcode for a non-rowid table.
sqlite> create table rowidPK ...> ( ...> x integer, ...> y text, ...> primary key (x) ...> ); sqlite> create table separatePK ...> ( ...> x integer primary key desc, ...> y text ...> ); sqlite> pragma table_info(rowidPK); cid|name|type|notnull|dflt_value|pk 0|x|integer|0||1 1|y|text|0||0 sqlite> pragma table_info(separatePK); cid|name|type|notnull|dflt_value|pk 0|x|integer|0||1 1|y|text|0||0 sqlite> explain select x from rowidPK not indexed; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 8 0 00 Start at 8 1 OpenRead 0 3 0 0 00 root=3 iDb=0; rowidPK 2 Explain 0 0 0 SCAN TABLE rowidPK 00 3 Rewind 0 7 0 00 4 Rowid 0 1 0 00 r[1]=rowid 5 ResultRow 1 1 0 00 output=r[1] 6 Next 0 4 0 01 7 Halt 0 0 0 00 8 Transaction 0 0 10 0 01 usesStmtJournal=0 9 Goto 0 1 0 00 sqlite> explain select x from separatePK not indexed; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 8 0 00 Start at 8 1 OpenRead 0 4 0 1 00 root=4 iDb=0; separatePK 2 Explain 0 0 0 SCAN TABLE separatePK 00 3 Rewind 0 7 0 00 4 Column 0 0 1 00 r[1]=separatePK.x 5 ResultRow 1 1 0 00 output=r[1] 6 Next 0 4 0 01 7 Halt 0 0 0 00 8 Transaction 0 0 10 0 01 usesStmtJournal=0 9 Goto 0 1 0 00 sqlite> It's too bad explain can't be used as a sub-query though. sqlite> select opcode from (explain select x from rowidPK not indexed); Error: near "select": syntax error -----Original Message----- From: sqlite-users [mailto:[email protected]] On Behalf Of Clemens Ladisch Sent: Tuesday, November 28, 2017 3:39 AM To: [email protected] Subject: Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools Peter Halasz wrote: > However, it's quite clear to me, as only a user of just a few of these > tools and libraries, that they would be improved greatly if they had a > direct way of querying which field in a SQLite table was acting as an alias > of ROW ID. The rowid column must have the type "integer", and must be the only column in the primary key: SELECT name FROM pragma_table_info('MyTable') WHERE type = 'integer' COLLATE NOCASE GROUP BY pk != 0 HAVING max(pk) = 1; (Before version 3.16.0, this was harder without pragma_table_info().) But there is no reliable way to check for the PRIMARY KEY DESC exception, or whether a table is a WITHOUT ROWID table. You could try to read the rowid/oid/_rowid_ columns, as long as the table does not use all three of these names for other columns. Regards, Clemens _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
