Snowflake - copy column headers for empty row

I am using copy into command to store the query results from snowflake into a file in s3 bucket. It works as expected but when the query returns no data I still want to save the column names in the same file so that I can display the empty row with column names in UI. I have specified header=true in the command but it doesn't work when the query returns no result.

Thanks

According to the docs:

If the source table contains 0 rows, then the COPY operation does not unload an empty data file; not even a file containing the table column headings.

And there doesn't seem to be a way to change this behavior.

However, before writing the table to S3, you could write the headers (as data) by querying the INFORMATION_SCHEMA. Then write your table as usual (with OVERWRITE=TRUE) -- and if no rows are written, the header file will remain.

---

UPDATE

Here's an how to generate a header:

select listagg(column_name,',') within group (order by ordinal_position)
from MY_DB.information_schema.columns 
where table_schema='MY_SCHEMA' and table_name='MY_TABLE';

You can then write it as data to a CSV file like this:

copy into @my_stage/my_table  (
    select listagg(column_name,',') 
        within group (order by ordinal_position)
    from MY_DB.information_schema.columns 
    where table_schema='MY_SCHEMA' and table_name='MY_TABLE';
)
file_format = (type=csv compression=none field_delimiter=none)
header=false;