SHOW ALL TABLES fails after MSSQL attach

[sashapodgoreanu]

Description

After attaching a SQL Server database using the mssql extension, basic queries work correctly, but SHOW ALL TABLES fails.

Environment

• OS: Windows
• DuckDB version: 1.5
• SQL Server: 2022
• Extension: mssql (hugr-lab)

Steps to Reproduce

1. Load the extension:
   LOAD mssql;

2. Attach a SQL Server database (using either SECRET or connection string):
   ATTACH 'connection_string' AS mssql_db (TYPE MSSQL);

3. Run a query:
   SELECT * FROM mssql_db.some_table;
   -- Works correctly

4. Run:
   SHOW ALL TABLES;

Expected Behavior

SHOW ALL TABLES should list tables from all attached databases, including MSSQL.

Actual Behavior

The command hangs with no error.

Notes

• Direct SELECT queries on MSSQL tables work fine.
• The issue seems specific to metadata listing.

[VGSML]

Thanks for reporting this. Could you provide the error?
What the version of the sql server and deployment azure/selfhosted? How many tables/schemas

[sashapodgoreanu]

Hello,

We are using a self-hosted SQL Server 2022 instance.
Our environment is quite large, with approximately 10,000 schemas and between 100 and 10,000 tables per schema.

I can confirm that SHOW ALL TABLES does not fail. However, the query executed by the extension in this phase (to read metadata from the attached database) is extremely slow in our scenario.

The main issue is not SHOW ALL TABLES itself, but what happens afterward. When we attempt to attach another database, DuckDB triggers a scan that results in reading metadata from SQL Server.
In our case, this metadata scan is very expensive and causes the operation to hang or take an excessive amount of time.

We created a Test DB, created 10000 Schemas with 0 tables, and the show all tables is very slow! 30 minutes and still running.

I enabled debug and i see the queries scrolling in the terminal.

[sashapodgoreanu]

Can "lazy loading" of metadatata of a schema/table be an option as a new feature?
We can use schema_filter to filter some schemas, but 10K tables we need them all.

Thanks.

[VGSML]

The main problem is the big number of schemas. Show all tables requests all metadata for all tables (including columns).
Did you try this: https://github.com/hugr-lab/mssql-extension#mssql_preload_catalog?
combining with schema_filter?

The main problem is in the number of schemas, the duckdb makes sequential calls for each, so we have 10k requests at least.

[VGSML]

10K tables it's not a big deal, I have tested on 200K (around 8M columns summary) it takes around 1 minutes to preload.
Unfortunately DuckDB do not suppose any optimization on catalog loading.

Do you use the DuckDB UI?

[sashapodgoreanu]

Hello,

We are using DuckDB CLI for these tests.

Using schema_filter together with mssql_preload_catalog helped.
With ~10K tables in a single schema, preload still takes around 10–15 seconds.

In our use case, we frequently create and destroy DuckDB instances (for data isolation), so this overhead is not acceptable.

We understand that DuckDB currently performs sequential catalog loading.
However, we have seen this handled differently in another extension, where metadata is loaded lazily (on-demand), and it worked well.

Would it be possible to adopt a similar approach here, loading metadata only when actually needed?

[VGSML]

This is the duckdb limitation when the show all tables is querying all catalog metadata schema by schema.
I didn't face the issue with attach second db requires catalog loading, I will take a look what I can do and why duckdb requests catalog before attach the new db.