CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schema
rdrgtec
rdrgtec

Reputation: 640

How to list which views consume a specific view or table in Snowflake?

I wanted to list or retrieve somehow which specific views selects data from a specific view or table in Snowflake. Something like:

SHOW VIEWS ... SELECTING FROM my_view

It would also be useful to know if it is possible to do that with Functions and Stored Procedures as well.

Upvotes: 1

Views: 7411

Answers (2)

Nikita Kulkarni
Nikita Kulkarni

Reputation: 61

You could query the get_object_references() table function to get which view is referencing which other view.

Documentation reference: https://docs.snowflake.com/en/sql-reference/functions/get_object_references.html

Another alternative is to query the OBJECT_DEPENDENCIES view in the SNOWFLAKE.ACCOUNT_USAGE SCHEMA

Documentation Reference : https://docs.snowflake.com/en/sql-reference/account-usage/object_dependencies.html

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176114

It could be retrieved from Object Dependencies

Query the OBJECT_DEPENDENCIES view in the Account Usage schema of the shared SNOWFLAKE database to determine the object references for the table SALES_NA

SET obj_name = 'my_view';

with recursive referenced_cte
(object_name_path, referenced_object_name, referenced_object_domain, referencing_object_domain, referencing_object_name, referenced_object_id, referencing_object_id)
    as
      (
        select referenced_object_name || '-->' || referencing_object_name as object_name_path,
               referenced_object_name, referenced_object_domain, referencing_object_domain, referencing_object_name, referenced_object_id, referencing_object_id
          from snowflake.account_usage.object_dependencies referencing
          where true
            and referenced_object_name = $obj_name and referenced_object_domain='TABLE'

        union all

        select object_name_path || '-->' || referencing.referencing_object_name,
              referencing.referenced_object_name, referencing.referenced_object_domain, referencing.referencing_object_domain, referencing.referencing_object_name,
              referencing.referenced_object_id, referencing.referencing_object_id
          from snowflake.account_usage.object_dependencies referencing join referenced_cte
            on referencing.referenced_object_id = referenced_cte.referencing_object_id
            and referencing.referenced_object_domain = referenced_cte.referencing_object_domain
      )

  select object_name_path, referenced_object_name, referenced_object_domain, referencing_object_name, referencing_object_domain
    from referenced_cte
;

Upvotes: 3

Related Questions