In snowflake, we use the "show databases"
command to see details about default and created databases, this command shows a
lot of details about databases, what if we just need the names of the database
not whole details about them, after digging out I found this
"databases" is table present in INFORMATION_SCHEMA schema, so we can
query the details from "databases" tables.
To fetch details you need to select the data warehouse,
database, and schema
technosnow#(no warehouse)@(no database).(no schema)>use WAREHOUSE TECHNO_WS;
+----------------------------------+
| status
|
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.644s
technosnow#TECHNO_WS@(no database).(no schema)>use SNOWFLAKE;
+----------------------------------+
| status
|
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.810s
technosnow#TECHNO_WS@SNOWFLAKE.(no schema)>use schema INFORMATION_SCHEMA;
+----------------------------------+
| status
|
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.313s
technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>desc
databases;
+----------------+-------------------+--------+-------+---------+-------------+------------+-------+------------+-----------------------------------------------------------------+-------------+
| name | type
| kind | null? | default
| primary key | unique key | check | expression | comment
| policy name |
|----------------+-------------------+--------+-------+---------+-------------+------------+-------+------------+-----------------------------------------------------------------+-------------|
| DATABASE_NAME | VARCHAR(16777216) | COLUMN | N
| NULL | N |
N | NULL | NULL
| Name of the database
| NULL |
| DATABASE_OWNER | VARCHAR(16777216) | COLUMN | N
| NULL | N | N
| NULL | NULL |
Name of the role that owns the schema
| NULL
|
| IS_TRANSIENT | VARCHAR(3)
| COLUMN | Y | NULL | N
| N | NULL |
NULL | Whether this is a transient table
| NULL |
| COMMENT | VARCHAR(16777216) |
COLUMN | Y | NULL | N
| N | NULL |
NULL | Comment for this database
| NULL |
| CREATED | TIMESTAMP_LTZ(9) |
COLUMN | Y | NULL | N
| N | NULL |
NULL | Creation time of the database
| NULL |
| LAST_ALTERED | TIMESTAMP_LTZ(9) | COLUMN |
Y | NULL | N
| N | NULL | NULL
| Last altered time of the database
| NULL |
| RETENTION_TIME | NUMBER(9,0) |
COLUMN | Y | NULL | N
| N | NULL | NULL
| Number of days that historical data is retained for Time
Travel | NULL |
+----------------+-------------------+--------+-------+---------+-------------+------------+-------+------------+-----------------------------------------------------------------+-------------+
7 Row(s) produced. Time Elapsed: 0.402s
Here you see the columns for the "databases"
table, now you can fetch anything you want
technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>select
database_name from databases;
+-----------------------+
|
DATABASE_NAME |
|-----------------------|
|
EXERCISE_DB |
|
SNOWFLAKE_SAMPLE_DATA |
|
TECHNODB |
+-----------------------+
3
Row(s) produced. Time Elapsed: 1.093s
technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>
[Also Read - Different Editions of Snowflake]
[Also Read - How to Undrop database]
0 comments:
Post a Comment