Method to get current database context for select query

[10bas10]

Hi All,

Im currently taking a look at reading the schema of a set of databases to be able to retrieve the datatype of columns used withing a query. In T-SQL you are able to change the database context and from that perspective you can refer to tables by the schema.table instead of a database.schema.table.

Is there already a method in place for setting the database context?

[tobymao]

when you call the optimizer on on a sql query, you can pass in a multi level nested dict

database -> schema -> table, or just schema -> table. if your queries don't use database because they have an inferred schema, then just use the schema -> table mapping

[10bas10]

Its kind of a mixed mode at our repository. For example:

USE db_test

SELECT 
 a.x
,b.y
FROM dbo.a
INNER JOIN db_test2.dbo.b
ON a.id = b.id

[10bas10]

I was thinking to add a parameter to the annotate_types, where you can add the current context database which is default when none specified in the query itself. What do you think?

[barakalon]

Check out qualify_tables

In your context, catalog->database and db->schema.

The main optimize function accepts those parameters as well.

[tobymao]

i think this functionality exists already as barak said, just need to run qualify first

[10bas10]

Great, exactly what i was looking for. Thanks!