Getting a list of schema + tables from a warehouse

[russelp]

Hi,

I am trying to automate the creation of shortcuts via the API and one requirement that we have is to create shortcuts in a lakehouse from a warehouse. I am already able to do this if there is only 1 schema in the warehouse since I can just hardcode the shortcut target path to the proper schema. However, if there are multiple schemas, a different approach is needed. 

Currently, the list_tables from the sempy fabric package can only return the tablenames but not the schemas. 

Is there a way to get a list of schemas + tables from a warehouse through the API or the sempy fabric package?

I have tried creating a shortcut in the lakehouse to access the information_schema but it's a view so shortcuts cannot be created. I have also tried creating a table version of the information_schema but looks like it's not supported (https://community.fabric.microsoft.com/t5/Data-Pipelines/Fabric-warehouse-create-table-as/m-p/359357...)

[Anonymous · Accepted solution]

Hi @russelp 
Thanks for using Fabric Community.
Currently we cannot get the information regarding schemas in Warehouse using Fabric Notebooks. 

However, your suggestion is definitely valuable! We use customer feedback like yours to prioritize future features. The more users who request the ability to customize backgrounds, the higher it moves on our list.

Appreciate if you could share the feedback on our Microsoft Fabric Ideas. Which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.

I hope this information helps. If you have any further queries please do let us know.

Thanks

[russelp]

I see, thank you for the info! 

[Anonymous]

I know this is a year old. But just in case anyone else is searching...

You could try:
creating a user data function w/ something like:

    query = f"""

    SELECT '{warehouse}' as WarehouseName,

        s.name AS SchemaName

    FROM

        {warehouse}.sys.schemas s

    """

then in notebook get token, call user data function, get results, transform results as needed. This uses the user data function as a bridge between querying the compute node and saving to storage node.