How to store the column names of a table in an array in Snowflake

user_13138455 0
Viewed 670

I am very new to Snowflake. I need to retrieve only the column names of a table and then return it as an array in a stored procedure.

Example:

   Column_A    Column_B    Column_C    Column_D
      1           2           2           2

should return [ Column_A, Column_B, Column_C, Column_D]

Here is my attempt:

create or replace procedure sample()
    return RETURNTYPE?
    language javascript
    AS
    $$
      var cmd = 'SELECT COLUMN_NAME' FROM table1;
      var statement = snowflake.createStatement({sqlTest: cmd});
      var res = statement.execute();
      var arr = [];
      //HOW CAN I PUSH COLUMN NAMES INTO THIS ARRAY
      
      return ARRAY;

    $$;

I new to this. Can someone explain how to solve this.

snowflake-cloud-data-platform stored-procedures sql javascript
2 Answers

I would simply leverage information_schema and the array_agg() function to get this done, rather than a stored procedure. Something like this works without all of the javascript as a native function in Snowflake:

SELECT array_agg(column_name)
FROM information_schema.columns
where table_name = 'TABLE_NAME';
user_121789800

Check Mike Walton's solution that doesn't use stored procedures. Leaving this answer up for anyone looking for an answer with stored procedures.

One solution is to run a SELECT * within the stored procedure, and then the statement will have the name of the columns:

CREATE OR REPLACE PROCEDURE get_columns(TABLE_NAME VARCHAR)
RETURNS ARRAY
LANGUAGE JAVASCRIPT
AS
$$
var stmt = snowflake.createStatement({
    sqlText: "SELECT * FROM " + TABLE_NAME + " LIMIT 1;",    
});
stmt.execute();

var cols=[];
for (i = 1; i <= stmt.getColumnCount(); i++) {
  cols.push(stmt.getColumnName(i));
}
return cols
$$
;
user_1324380
Related