Re: Find schema of a table in a query

On Sunday, 19 January, 2020 01:47, x <[email protected]> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users