Noel Frankinet wrote:
Lawrence Chitty wrote:
You may be able to do this using the 'explain' statement
for example, I have a view called 'myview'. Wrapping this into an sql
select statement and proceeding this with explain e.g.
> explain select null from myview
gives a result set with the following:-
addr opcode p1 p2 p3
0 ColumnName 0 0 null
1 ColumnName 1 0 TEXT
2 Integer 0 0 <NULL>
3 OpenRead 1 228 mytable1
4 VerifyCookie 0 3016 <NULL>
5 Integer 0 0 <NULL>
6 OpenRead 2 17 mytable2
7 Rewind 1 13 <NULL>
8 Rewind 2 12 <NULL>
9 String 0 0 <NULL>
10 Callback 1 0 <NULL>
11 Next 2 9 <NULL>
12 Next 1 8 <NULL>
13 Close 1 0 <NULL>
14 Close 2 0 <NULL>
15 NullCallback 1 0 <NULL>
16 Halt 0 0 <NULL>
All you need to do now is search through the result set for any
opcode that is OpenRead, and the table name is in the p3 (last)
column. As you can see, myview consists of mytable1 and mytable2
Thank you that's what I was looking for.
Noel and Lawrence,
You should note that this will only work if your sqlite library is
compiled without NDEBUG defined (i.e. only in a debug build). In debug
builds, the table names in the explain output are generated by taking
the root page number that is actually stored in the compiled VM and
looking up the corresponding table name in the sqlite_master table.
Production builds (which are substantially faster) don't do this, they
simply display the root page number.
You can of course do the lookup in the sqlite_master table yourself, but
I don't know if there is a good way to tell if you are using a debug or
production build. You can't simply check if the table name is numeric
because someone could create a table with a numeric name. Some of the
pragmas, like parser_trace, don't work in production builds, but don't
generate any errors either.
If anyone knows how to tell if the sqlite library was built with NDEBUG
defined, this could be made to work well enough.
HTH
Dennis Cote
