SQLAlchemy does not return all table names in SQL Server database

Question

When I download this example database, AdventureWorksLT2012_Data, and try to access the table_names in sql alchemy, via

from sqlalchemy import create_engine
engine = create_engine("mssql+pyodbc://sa:PASSWORD_HERE@localhost:1433/AdventureWorksLT?driver=FreeTDS")
engine.table_names()

I get ['BuildVersion', 'ErrorLog']. However this is missing tables from the database.

For example, executing this query gives the expected table names...

rs = engine.execute("SELECT name FROM sys.Tables")
[row['name'] for row in rs]

I get

['BuildVersion', 'Address', 'Customer', 'CustomerAddress', 'Product', 'ProductCategory', 'ProductDescription', 'ProductModel', 'ProductModelProductDescription', 'SalesOrderDetail', 'SalesOrderHeader', 'ErrorLog']

Is this a bug, or have I overlooked something? If I create a new database, and tables with identical names as above, then engine.table_names() works as expected.

Answer 1

Looking at the corresponding database creation script, the two tables you get from engine.table_names() are the ones that are created in the [dbo] schema. The others are created in the [SalesLT] schema.

So it appears that engine.table_names() is only listing tables for the default schema in effect at the time it is invoked, which in this case happens to be [dbo].