Is there any function or way to retrieve table names from Snowflake in the order of referential integrity (FK) dependencies?

Question

I would like to retrieve table names from a given schema in the sorted order based on their foreign key dependencies. For example, if I have three following tables created in Snowflake

CREATE TABLE TAB_X
(
    COL_A CHAR(18),
    COL_B CHAR(18),
    COL_C CHAR(18),
    CONSTRAINT XPKTAB_X PRIMARY KEY (COL_A, COL_B)
);

CREATE TABLE TAB_Z
(
    COL_D CHAR(18),
    COL_E CHAR(18),
    COL_F CHAR(18),
    COL_G CHAR(18),
    COL_A CHAR(18),
    COL_B CHAR(18),
    CONSTRAINT XPKTAB_Z PRIMARY KEY (COL_D, COL_E, COL_A, COL_B),
    CONSTRAINT R_1 FOREIGN KEY (COL_A, COL_B) REFERENCES TAB_X (COL_A, COL_B)
);

CREATE TABLE TAB_B
(
    COL_H CHAR(18),
    COL_I CHAR(18),
    COL_J CHAR(18),
    COL_K CHAR(18),
    COL_D CHAR(18),
    COL_E CHAR(18),
    COL_A CHAR(18),
    COL_B CHAR(18),
    CONSTRAINT XPKTAB_B PRIMARY KEY (COL_H, COL_I, COL_D, COL_E, COL_A, COL_B),
    CONSTRAINT R_2 FOREIGN KEY (COL_D, COL_E, COL_A, COL_B) REFERENCES TAB_Z (COL_D, COL_E, COL_A, COL_B)
);

In this scenario, if I execute a SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES; query then it would return me the table names in alphabetically sorted order, that will be.

TAB_B
TAB_X
TAB_Z

But I want the correct order of creation, that is:

TAB_X
TAB_Z
TAB_B

Any help with this would be very much appreciated.

Update: I cannot achieve this by sorting the table names based on CREATION timestamp, for example, if I create the Tables as following:

CREATE TABLE TAB_A
(
    COL_1 CHAR(18),
    COL_2 CHAR(18),
    COL_3 CHAR(18),
    COL_6 CHAR(18),
    COL_8 CHAR(18),
    COL_12 CHAR(18)
);

ALTER TABLE TAB_A
    ADD CONSTRAINT XPKCOL_C PRIMARY KEY (COL_1, COL_2, COL_3, COL_6, COL_8);

CREATE TABLE TAB_C
(
    COL_1 CHAR(18),
    COL_2 CHAR(18),
    COL_3 CHAR(18),
    COL_6 CHAR(18),
    COL_8 CHAR(18),
    COL_11 CHAR(18)
);

ALTER TABLE TAB_C
    ADD CONSTRAINT XPKCOL_X PRIMARY KEY (COL_1, COL_2, COL_3, COL_6, COL_8);

CREATE TABLE TAB_O
(
    COL_1 CHAR(18),
    COL_2 CHAR(18),
    COL_3 CHAR(18),
    COL_6 CHAR(18),
    COL_8 CHAR(18),
    COL_12 CHAR(18)
);

ALTER TABLE TAB_O
    ADD CONSTRAINT XPKCOL_A PRIMARY KEY (COL_1, COL_2, COL_3, COL_6, COL_8);

CREATE TABLE TAB_X
(
    COL_1 CHAR(18),
    COL_2 CHAR(18),
    COL_3 CHAR(18),
    COL_4 CHAR(18),
    COL_5 CHAR(18)
);

ALTER TABLE TAB_X
    ADD CONSTRAINT XPKTAB_A PRIMARY KEY (COL_1, COL_2, COL_3);

CREATE TABLE TAB_Z
(
    COL_1 CHAR(18),
    COL_2 CHAR(18),
    COL_3 CHAR(18),
    COL_6 CHAR(18),
    COL_8 CHAR(18),
    COL_9 CHAR(18),
    COL_0 CHAR(18)
);

ALTER TABLE TAB_Z
    ADD CONSTRAINT XPKTAB_Z PRIMARY KEY (COL_1, COL_2, COL_3, COL_6, COL_8);

ALTER TABLE TAB_A
    ADD CONSTRAINT R_4 FOREIGN KEY (COL_1, COL_2, COL_3, COL_6, COL_8) REFERENCES TAB_C (COL_1, COL_2, COL_3, COL_6, COL_8);

ALTER TABLE TAB_C
    ADD CONSTRAINT R_2 FOREIGN KEY (COL_1, COL_2, COL_3, COL_6, COL_8) REFERENCES TAB_Z (COL_1, COL_2, COL_3, COL_6, COL_8);

ALTER TABLE TAB_O
    ADD CONSTRAINT R_3 FOREIGN KEY (COL_1, COL_2, COL_3, COL_6, COL_8) REFERENCES TAB_C (COL_1, COL_2, COL_3, COL_6, COL_8);

ALTER TABLE TAB_Z
    ADD CONSTRAINT R_1 FOREIGN KEY (COL_1, COL_2, COL_3) REFERENCES TAB_X (COL_1, COL_2, COL_3);

Greg Pavlik Greg Pavlik · Accepted Answer · 2020-04-02T16:41:19

The INFORMATION_SCHEMA.TABLES view has a CREATED timestamp. You can return them ordered by timestamp:

select TABLE_NAME from INFORMATION_SCHEMA.TABLES order by CREATED;

If you're looking for something else, let me know. Thanks.

Is there any function or way to retrieve table names from Snowflake in the order of referential integrity (FK) dependencies?

2 Answers