snowflake-cloud-data-platform

List all tables within a select statement in Snowflake

I have query in Snowflake that is near a thousand line. Is there a way to easily see what tables are accessed to easily list it? This will need to be done for many files, so if there is a quick & easy way to list all the tables it would be wonderful.

Solution

  • If you have query_ids for these queries. You can probably get them using access_history snowflake account_usage table. Look here for access history view details, and you probably need GOVERNANCE_VIEWER database role to access snowflake.account_usage.access_history table

    
select
    boa.value:"objectName" as table_name
from snowflake.account_usage.access_history
, lateral flatten(base_objects_accessed) boa
where query_id = '<query_id>'
and  boa.value:"objectDomain"::string='Table';