I’m trying to create a SQL function like below, I was passing databasename, schemaname and table name as input. I was not able to use the input database name for the information_schema
below is my Snowflake function:
CREATE OR REPLACE FUNCTION FN_GET_COLUMNS(DatabaseName VARCHAR,SchemaName VARCHAR,TableName VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
WITH data_columns AS (
SELECT UPPER(column_name) AS column_name
FROM information_schema.columns
WHERE table_catalog = UPPER(DatabaseName)
AND table_schema = UPPER(SchemaName)
AND table_name = UPPER(TableName)
AND is_identity != 'YES'
) SELECT LISTAGG('src.'||column_name, ', ') within group(order by column_name) as data_col
FROM data_columns
WHERE NOT STARTSWITH(column_name,'__')
$$;
I was not able to use DatabaseName.information_schema.columns
[Where DatabaseName is the input argument]?
Any solution for this?