WOW Keith. That’s the sqlite coding equivalent of scoring a hat trick in the
world cup final. I’ve added an order by to get the solution
select name from pragma_database_list d
where (select name from pragma_table_xinfo where schema==d.name and arg==?1)
order by seq!=1, seq limit 1;
I’m assuming the temp db is always 1 in the seq column. Can anyone confirm that
or should I change it to
order by lower(name)!=temp, seq limit 1;
Thanks. I also learned the parentheses are not required for pragma functions
when there’s no params and alternate syntax when they are.
________________________________
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of
Keith Medcalf <kmedc...@dessus.com>
Sent: Sunday, January 19, 2020 8:32:06 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Find schema of a table in a query
On Sunday, 19 January, 2020 01:47, x <tam118...@hotmail.com> wrote:
>Suppose you’re given a query ‘SELECT 1 from tbl’ by a user and you want
>to know the name of the schema that tbl belongs to. What’s the easiest
>way to do this?
>I know sqlite will use temp.tbl if it exists else main.tbl if it exists
>else it will search for the earliest attached schema with a table called
>tbl. Finding that involves the use of PRAGMA database_list and then
>querying each of the associated sqlite_master tables in turn for the
>existence of tbl until you get a match. Is there an easier way?
How about:
select name
from pragma_database_list as d
where (select name
from pragma_table_xinfo
where schema == d.name
and arg == 'x') is not null;
Where you set "arg == 'tablename'" which will return all the schema's in which
the specified tablename exists.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
