CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schemapii
Sayantani Bhattacharjee
Sayantani Bhattacharjee

Reputation: 1

Extract schema name based on current Snowflake column

I am working on a Snowflake tag and masking policy combination and was poking around to see if there is a way to dynamically extract the schema_name of the current working column. I was not able to find anything that matched the requirement. Is it possible to extract this information? Perhaps something similar to the SYSTEM$GET_TAG_ON_CURRENT_COLUMN('<tag_name>') function? It doesn't have to extract just schema name, the result can be the Snowflake column object details like database_name, schema_name, etc.

Tried looking in snowflake documentation but could only find current_schema() which works only according to the current session database and not current column.

Upvotes: 0

Views: 193

Answers (2)

Matthew Coudert
Matthew Coudert

Reputation: 1

From the Snowflake docs:

If you call this function in the body of a masking or row access policy, in the handler code of a UDF, or in the definition of a view, the function returns the following:

In a policy, the function returns the database or schema that contains the table or view that is protected by the policy.

In a UDF, the function returns the database or schema that contains the UDF.

In a view, the function returns the database or schema that contains the view.

You should be able to use current_schema() in your masking policy to return the schema of the schema that contains the table or view that is protected by the policy.

Upvotes: 0

Ph1reman
Ph1reman

Reputation: 576

There are many ways to extrac the schema of a specific column, and the best approach depends on what you're doing exactly. As there is not much information here, I can only guess. But the information_schema has a table called columns that holds a lot of information for the specific column. Do however note that a column name as you understand is not unique in nature. For any given column name that I have in my databases, there are multiple results coming back, all of them correct.

About your original mention of CURRENT_SCHEMA() I don't quite follow as it will indeed return the SCHEMA that you've got selected in your session. CURRENT_DATABASE() is what you'd use for the selected database in session, just like CURRENT_ROLE(), CURRENT_SECONDARY_ROLES() etc will list the roles for the session. Having that said, If you have not selected a SCHEMA for the session, public will be assumed.

This can be used to look at a specific column by text, and if you have it in session you should also already have the database and table information.

SELECT *
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    COLUMN_NAME = 'COLUMN NAME HERE';

As I'm not aware of your use case it's impossible to say more than this I feel like, but we have a lot of tools available to us for this if you have the right level of access.

Upvotes: 0

Related Questions