Re: Find schema of a table in a query

On 1/20/20 4:17 AM, Keith Medcalf wrote:
> The "main" database is always seq == 0, the "temp" database is always seq == 1, and other databases are seq == 2 and greater in the order they were attached.  seq 2 -> whatever is always contiguous.  The table search order for unqualified names (when a search is required) is always in the seq returned by pragma database_list, so
>
> select name
>    from pragma_database_list as d
>   where exists (select *
>                   from pragma_table_info
>                  where schema == d.name
>                    and arg == 'x')
>   limit 1;
>
> will always return the correct schema name, no order by required.  (and you can use table_info rather than table_xinfo because a table must always have one named column that is not hidden.  If this were not the case, then you would have to use table_xinfo to ensure that something is returned for that pragma lookup.
>
One point, due to the nature of the fundamentals of SQL, the whole 
concept of 'first' means you MUST have an ORDER BY to get the item you 
want.  Yes, when you test it may seem that the records always come in 
the order you want without it, but that is really happenstance, and 
might change by any number of things.

If the order that the SQL engine happens to choose to fetch the data 
does happen to be in the order specified by the ORDER BY, then a good 
engine will optimize it out, so it is free.

-- 
Richard Damon

_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users