page301
389
USE ROLE IDENTIFIER ( $tpc_owner_role );
USE DATABASE IDENTIFIER ( $tpc_database );
USE SCHEMA IDENTIFIER ( $tpc_owner_schema );
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_xs );
Somewhat surprisingly, Snowflake does not provide a single
account_usage
or
information_schema
view to identify all objects, their types, and their location.
Investigating the available
account_usage
or
information_schema
views may return
misleading results. For example, dynamic tables are referenced as type
NULL
.
SHOW
always returns information for the current role.
At the time of writing, using the
SHOW OBJECTS
command also returns the incorrect
object type of
VIEW
. Where you would expect to see
MATERIALIZED VIEW
, a change
request with Snowflake has been raised to normalize behavior.
SHOW OBJECTS;
However, you find issuing
SHOW
command for each target object type works as
expected. Note that each
SHOW
command returns differing attributes.
SHOW TABLES;
SHOW DYNAMIC TABLES;
SHOW EXTERNAL TABLES;
SHOW HYBRID TABLES;
SHOW VIEWS;
SHOW MATERIALIZED VIEWS;
Your objective is to extract a consistent form of metadata for each of the
SHOW
commands for consistent later use. This SQL statement works for the previous
SHOW
commands. Note that the
object_type
should be changed according to the desired
object type. You can also filter out objects created using supplied Snowflake roles.
SELECT "database_name"||'.'||"schema_name"||'.'||"name" AS path_to_object,
'TABLE | VIEW | etc' AS object_type,
"owner",
"owner_role_type"
ChapTer 10
OpTimizing perfOrmanCe