Empty database identifier in macro fabric_get_columns_in_relation

[itxffink]

If I want to query the columns of a relation the macro below does not contain the database but an empty pair of square brackets, which does not work for me.

original macro:

{% macro fabric__get_columns_in_relation(relation) -%}
{% set query_label = apply_label() %}
{% call statement('get_columns_in_relation', fetch_result=True) %}

    with mapping as (
        select
            row_number() over (partition by object_name(c.object_id) order by c.column_id) as ordinal_position,
            c.name collate database_default as column_name,
            t.name as data_type,
            c.max_length as character_maximum_length,
            c.precision as numeric_precision,
            c.scale as numeric_scale
        from [{{ 'tempdb' if '#' in relation.identifier else relation.database }}].sys.columns c {{ information_schema_hints() }}
        inner join sys.types t {{ information_schema_hints() }}
        on c.user_type_id = t.user_type_id
        where c.object_id = object_id('{{ 'tempdb..' ~ relation.include(database=false, schema=false) if '#' in relation.identifier else relation }}')
    )

    select
        column_name,
        data_type,
        character_maximum_length,
        numeric_precision,
        numeric_scale
    from mapping
    order by ordinal_position
    {{ query_label }}

{% endcall %}
{% set table = load_result('get_columns_in_relation').table %}
{{ return(sql_convert_columns_in_relation(table)) }}

{% endmacro %}

If I omit the square brackets, the macro works for the use case, where I want to get all the columns of a specific object.

[prdpsvs]

@itxffink , what is the scenario you are testing? I am surprised that relation does not contain database name

[itxffink]

Hi @prdpsvs , I tried to run a macro to list all the columns:

{% macro find_datatypes(model) %}
{% set cols=adapter.get_columns_in_relation(model) %}
{%- for col in cols %}
- name: {{ col.name | lower }}
data_type: {{ col.dtype | lower }}
{%- endfor %}
{% endmacro %}

{{ find_datatypes('nwd.fct_orders')}}

I found out, that I can overwrite the standard template in the fabric adapter with the following macro file:

{% macro fabric__get_columns_in_relation(relation) -%}
{% set query_label = apply_label() %}
{% call statement('get_columns_in_relation', fetch_result=True) %}

    with mapping as (
        select
            row_number() over (partition by object_name(c.object_id) order by c.column_id) as ordinal_position,
            c.name collate database_default as column_name,
            t.name as data_type,
            c.max_length as character_maximum_length,
            c.precision as numeric_precision,
            c.scale as numeric_scale
        
        from {{ 'tempdb' if '#' in relation.identifier else '[' ~ relation.database ~ ']' if relation.database != is_null else '' }}.sys.columns c {{ information_schema_hints() }}
        inner join sys.types t {{ information_schema_hints() }}
        on c.user_type_id = t.user_type_id
        where c.object_id = object_id('{{ 'tempdb..' ~ relation.include(database=false, schema=false) if '#' in relation.identifier else relation }}')

    )

    select
        column_name,
        data_type,
        character_maximum_length,
        numeric_precision,
        numeric_scale
    from mapping
    order by ordinal_position
    {{ query_label }}

{% endcall %}
{% set table = load_result('get_columns_in_relation').table %}
{{ return(sql_convert_columns_in_relation(table)) }}

{% endmacro %}