How can I tell which columns use a sequence for default value in snowflake?

0
votes

I would like to make a list of columns in my Snowflake database that use a sequence.

The INFORMATION_SCHEMA schema contains views for Columns and Sequences but I don't see any table/view that would allow me to find out which sequence is used by which column.

Is it feasible through the INFORMATION_SCHEMA table?

Or is there another set of Snowflake specific metadata I could use instead?

2
snowflake-cloud-data-platform

2 Answers

1
votes

I do not believe there is an existing view in Snowflake that accomplishes your request. That would be a feature request.

It may be possible to workaround using a script that cycles/loops through your databases/tables and logs the output you are looking for. For example, the SHOW COLUMNS IN TABLE command will show the 'autoincrement' column which will contain the sequence used in populating the column.

0
votes

Actually, I got that wrong...

The column INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT contains a text reference to the sequence.

It's not a separate table but would do the job.

USE DATABASE ZZ_ERIC;
CREATE SCHEMA JUNK;
CREATE SEQUENCE JUNK.TOTO_SEQ;
CREATE TABLE JUNK.TOTO_TBL(TOTO_FIELD INTEGER NOT NULL DEFAULT JUNK.TOTO_SEQ.NEXTVAL, TOTO_NAME VARCHAR(100));

SELECT * 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'JUNK';

seems to work.

I get "ZZ_ERIC.JUNK.TOTO_SEQ.NEXTVAL" in COLUMN_DEFAULT

So I could get an answer to my initial question with something like this

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_DEFAULT LIKE '%.NEXTVAL';