特定スキーマ以下のカラムにTAGがついているテーブルを取得する

やりたいこと

カラムにTAGが付与されているテーブルをリストアップする

半分以上は以前のLAST_QUERY_IDをとってUNIONするというネタそのまま

TAG状況を確認したい時、カラムレベルのタグを確認する場合は
https://docs.snowflake.com/ja/sql-reference/functions/tag_references_all_columns

こちらの関数で取得することになりますが

この関数はテーブル一つ一つに対して実行するので、複数テーブルの確認をするには少し不便。

毎回テーブルごとにクエリ投げるのも、Snowsightからみるのも手間だったので
スキーマレベルで一括で取得できるストアドを作成しておく
(DBレベルだとちょっとテーブル数多くなりすぎるのでスキーマで切れる様に制限をつける）

処理概要

1. スキーマ以下のテーブルをINFORMATION_SCHEMAから取得

2. テーブルを片っ端からTAG_REFERENCES_ALL_COLUMNSにかける
   (この際、カラムに付与されているタグ分行が増えるので、ARRAY_AGGでグルーピング)

3. ↑のQUERY_IDをLAST_QUERY_IDから取得し、ARRAYに保存

4. QUERY_IDのARRAYをFLATTENし、行に復元結果をSELECTする

5. QUERY_IDごとにRESULT_SCANしつつUNIONするクエリを作成

6. 実行して結果をTABLEとしてRETURN

というわけでストアド

SQL

WITH GET_TAG_TABLE_ON_SCHEMA AS PROCEDURE ( TARGET_SCHEMA TEXT )
    RETURNS TABLE()
    LANGUAGE SQL
AS
$$
    DECLARE
        TABLE_RESULT RESULTSET;
        --INFOMATION_SCHEMAからテーブル取得するクエリ
        GET_INFO_QUERY TEXT DEFAULT 'SELECT CONCAT_WS(''.'',TABLE_SCHEMA,''"'' ||TABLE_NAME||''"'') as FULLPATH,TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME
            FROM TESTDB.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ?';
        QUERIES ARRAY DEFAULT [];
        RS RESULTSET;
    BEGIN
        TABLE_RESULT := (EXECUTE IMMEDIATE :GET_INFO_QUERY USING( TARGET_SCHEMA ));
        LET TABLE_CUR CURSOR FOR TABLE_RESULT;
    
        FOR T IN TABLE_CUR DO
            LET TFN TEXT := T.FULLPATH;

            --INFOMATION_SCHEMAから取得したテーブルについてTAG情報を取得
            LET QUERY TEXT := 'SELECT OBJECT_DATABASE,OBJECT_SCHEMA,OBJECT_NAME,COLUMN_NAME,ARRAY_AGG(TAG_NAME) FROM TABLE(TESTDB.INFORMATION_SCHEMA.TAG_REFERENCES_ALL_COLUMNS(''' || :TFN || ''', ''TABLE'')) GROUP BY ALL';
    
            EXECUTE IMMEDIATE :QUERY;
            QUERIES := ARRAY_APPEND(:QUERIES,LAST_QUERY_ID());
            
        END FOR;
    
        IF (ARRAY_SIZE(:QUERIES) > 0 ) THEN
            LET Q_RS RESULTSET := (SELECT VALUE FROM TABLE(FLATTEN( INPUT => :QUERIES)));
            LET Q_CUR CURSOR FOR Q_RS;
        
            LET GET_LIST_QUERY TEXT := '';
        
            FOR QID IN Q_CUR DO
                LET Q TEXT := QID.VALUE;
                IF (GET_LIST_QUERY != '') THEN
                    GET_LIST_QUERY := GET_LIST_QUERY || '\n UNION ALL \n';
                END IF;
    
                GET_LIST_QUERY := GET_LIST_QUERY || 'SELECT * FROM TABLE(RESULT_SCAN(''' || :Q || ''')) ';
    
            END FOR;
            RS := (EXECUTE IMMEDIATE :GET_LIST_QUERY);
            RETURN TABLE(RS);
        END IF;
    END;
$$
CALL GET_TAG_TABLE_ON_SCHEMA('TEST');

これを実行すると

テーブルとカラム、付与されてるタグ名が取得できます。