Snowflake SHOW
Get metadata information about data objects within a Snowflake account.
I am a software engineer with over two years of professional experience. I specialize as a backend engineer but also work in full-stack capabilities. I use JavaScript/TypeScript, Python, and PHP to solve real-world problems every day.
Similar to MySQL, you can use SHOW DATABASES to get information about your databases in Snowflake.
While I love PostgreSQL, it's not as straightforward. In Postgres, you need to query the pg_database table or use \l or \list in the psql shell.
Databases are not the only database objects you can get information about using SnowSQL. You can also get metadata information about schemas, views, pipelines, and even columns in a table using the SHOW clause.
Getting Metadata Information
Similar to databases, you can retrieve the tables linked to a specific schema by using:
SHOW TABLES;
The result looks something like this:
Note that there are at least five columns omitted in the screenshot above.
What if I wanted to fetch only the tables that belong to a database?
SnowSQL conveniently makes the SHOW statement resemble the SELECT statement. Hence, I can simply add IN <database_name> to the statement above.
SHOW TABLES IN DATABASE sales_data;
You can also query database objects that match a specific pattern using the LIKE clause:
SHOW DATABASES LIKE 'pg_%';
Or even combine it with IN DATABASE as shown below:
SHOW TABLES LIKE 'easter%' IN DATABASE easter_routines;
Like I stated above, you can get information about schemas and views too.
SHOW SCHEMAS IN DATABASE sales_data;
SHOW VIEWS IN DATABASE sales_data;
Or even extract metadata information about columns in a particular table:
SHOW COLUMNS IN TABLE games_night;
If you need more detailed information about your database or its objects, you can use the DESCRIBE command, e.g:
DESCRIBE TABLE games_night;
In PostgreSQL, you would use \d games_night in the psql command-line interface or query the information_schema to get the same information.
The information_schema is a standardized set of read-only views that provides metadata about the database's structure and objects, such as tables, columns, data types, and constraints.
Since Snowflake also allows you to create staging areas (also known as stages), you can also use the DESCRIBE command to get detailed information about a stage you created.
A staging area, in Snowflake, is an intermediate storage area where data is pulled from internal or external sources.
DESCRIBE STAGE fetch_csv;
Conclusion
I prefer not to include a conclusion in short articles like this. If you need a summary of what was discussed, please re-read the article.
Thank you for reading!
