How to Query Information Schema on Snowflake: Tips and Tools for 2025

Updated December 13th, 2024

Share this article

To query the Information Schema in Snowflake, you utilize a set of system-defined views that provide essential metadata about your databases, schemas, and tables.

See How Atlan Simplifies Data Governance – Start Product Tour

This schema allows you to execute SQL queries to retrieve valuable insights about your data warehouse.

Understanding how to effectively query this schema is crucial for optimizing database performance and managing data lineage.

Snowflake’s Information Schema is a set of system-defined views that provide metadata about your account, databases, schemas, tables, and other database objects. You can use SQL queries to access this metadata and gather insights about your data warehouse.

Table of contents #

  1. Steps to query Information Schema in Snowflake
  2. Top tools to query Snowflake’s Information Schema without writing SQL queries
  3. Leveraging Snowflake’s Information Schema for metadata management
  4. The benefits of deploying a metadata management tool for your Snowflake data warehouse
  5. How organizations making the most out of their data using Atlan
  6. Bringing it all together
  7. FAQs about How to Query Information Schema on Snowflake
  8. How to Query Information Schema on Snowflake? Related reads

Querying Snowflake Information Schema: Examples to get started #

To query the Information Schema in Snowflake, you need to follow this general structure:

SELECT * FROM <database_name>.information_schema.<view_name>;

Replace <database_name> with the name of your database, and <view_name> with the specific view you want to query within the Information Schema.

Here are some examples of common queries to get you started:

  1. List all databases
  2. List all schemas within a specific database
  3. List all tables within a specific schema
  4. List all columns for a specific table within a specific schema
  5. List all views within a specific schema

Now, let us learn how to create the queries:

1. List all databases #


SELECT * FROM snowflake.information_schema.databases;

2. List all schemas within a specific database #


SELECT * FROM <database_name>.information_schema.schemata;

3. List all tables within a specific schema #


SELECT * FROM <database_name>.information_schema.tables WHERE table_schema = '<schema_name>';

4. List all columns for a specific table within a specific schema #


SELECT * FROM <database_name>.information_schema.columns WHERE table_schema = '<schema_name>' AND table_name = '<table_name>';

5. List all views within a specific schema #


SELECT * FROM <database_name>.information_schema.views WHERE table_schema = '<schema_name>';

Remember to replace <database_name>, <schema_name>, and <table_name> with your actual database, schema, and table names.

These are just a few examples, but you can query many other views in the Information Schema to gather metadata about your Snowflake environment. The Snowflake documentation provides a comprehensive list of available views and their descriptions: https://docs.snowflake.com/en/sql-reference/info-schema.html

Top tools to query Snowflake’s Information Schema without writing SQL queries #

There are several tools available that allow you to query Snowflake’s Information Schema without having to write SQL queries manually. These tools provide graphical user interfaces (GUIs) that make it easier to explore your data warehouse and access metadata. Some popular options include:

  1. Snowflake Web Interface
  2. dbt (Data Build Tool)
  3. Data visualization and BI tools
  4. Database management tools

Let us look into each of the above tools in brief:

1. Snowflake Web Interface #


Snowflake provides its own web-based interface called Snowsight (formerly Snowflake Worksheet). It features an easy-to-use interface for managing and querying your data warehouse. While it still requires some SQL knowledge, Snowsight offers features like autocomplete, syntax highlighting, and query history to make the process easier.

You can access Snowsight by logging into your Snowflake account at https://<account_identifier>.snowflakecomputing.com/console/login.

2. dbt (Data Build Tool) #


dbt is an open-source data transformation tool that works with Snowflake. While it primarily focuses on managing data transformations, it also has a feature called “dbt docs” that generates a web-based interactive schema documentation from your dbt project. This can help you explore your Information Schema without writing SQL queries.

Learn more about dbt at https://www.getdbt.com/.

3. Data visualization and BI tools #


Many data visualization and business intelligence tools, such as Tableau, Looker, Power BI, and Sisense, offer connectors to Snowflake. These tools allow you to explore and visualize your data warehouse’s schema and tables through a graphical interface, without the need to write SQL queries. Keep in mind that some of these tools require a paid license.

4. Database management tools #


Some database management tools, like SQL Workbench/J, DBeaver, and DataGrip, provide support for Snowflake. These tools offer a GUI to browse your Snowflake data warehouse’s schema, tables, and other objects. They can help you view and manage your Information Schema with little or no SQL knowledge. While some of these tools are open-source and free, others require a paid license.

Each of these options has its own advantages and limitations, so you’ll need to choose the one that best fits your requirements and preferences.

Leveraging Snowflake’s Information Schema for metadata management #

Metadata management tools help organizations maintain, discover, and understand their data assets by providing a centralized system for managing metadata. They usually offer a graphical user interface (GUI) to explore, catalog, and document data assets, making it easier for users to find and understand the data they need.

Metadata management tools leverage the Information Schema in Snowflake by:

  1. Extracting metadata
  2. Data lineage
  3. Data cataloging
  4. Data profiling
  5. Data governance
  6. Integration with other tools

Let us look into each of the above metadata management tools in brief:

1. Extracting metadata #


These tools connect to Snowflake’s Information Schema and extract metadata about databases, schemas, tables, columns, views, and other database objects. This metadata typically includes object names, data types, table relationships, and other structural information.

2. Data lineage #


Metadata management tools can use the information from the Information Schema to trace data lineage, showing how data flows through your organization’s data pipeline. This can help you understand dependencies and the impact of changes on your data assets.

3. Data cataloging #


The tools create a searchable data catalog that allows users to easily find and understand their data assets. This catalog typically includes metadata extracted from the Information Schema, as well as user-generated documentation, tags, and annotations.

4. Data profiling #


Some metadata management tools offer data profiling features, which use the Information Schema metadata to provide statistics and insights about your data, such as column value distributions, data quality issues, and data anomalies.

5. Data governance #


Metadata management tools can enforce data governance policies and processes by leveraging the Information Schema. For example, they can monitor and control access to sensitive data or enforce naming conventions and data quality rules.

6. Integration with other tools #


Metadata management tools often integrate with other data management tools, such as ETL platforms, data visualization tools, and data quality tools. By leveraging the Information Schema in Snowflake, they can ensure consistency and accuracy across your entire data stack.

Popular metadata management tools that can work with Snowflake include Alation, Collibra, Informatica Enterprise Data Catalog, and Apache Atlas. These tools vary in features, pricing, and ease of use, so you should evaluate them based on your organization’s specific needs and requirements.

Benefits of deploying a metadata management tool for the Snowflake platform #

Deploying a metadata management tool can help you maximize the value drawn from Snowflake for several reasons:

  1. Improved discoverability
  2. A better understanding of data
  3. Data governance
  4. Increased collaboration
  5. Data lineage and impact analysis
  6. Reduced time to insight
  7. Integration with other data tools

Now, let us better understand each of the above reasons:

1. Improved discoverability #


A metadata management tool creates a centralized, searchable data catalog that allows users to easily find the data assets they need. This saves time and effort for data consumers, who no longer need to manually search through databases, schemas, and tables.

2. A better understanding of data #


Metadata management tools provide comprehensive documentation and context for your data assets, including data lineage, relationships, and user-generated descriptions. This enables data consumers to understand data more quickly and accurately, reducing the risk of errors and misinterpretations.

3. Data governance #


By centralizing metadata, these tools can help enforce data governance policies and processes. They can monitor and control access to sensitive data, enforce naming conventions, and maintain data quality standards, ensuring a consistent and compliant data environment.

4. Increased collaboration #


Metadata management tools promote collaboration between data producers, consumers, and stewards. They facilitate communication through shared documentation, annotations, and tagging, which can lead to improved data quality, better decision-making, and more efficient use of data resources.

5. Data lineage and impact analysis #


Understanding data lineage helps you track how data moves through your organization, from its source to its final destination. Metadata management tools provide insights into data lineage, making it easier to perform impact analysis and assess the consequences of changes in your data pipeline.

6. Reduced time to insight #


By making data more accessible and understandable, metadata management tools can speed up the time it takes for users to draw insights from your Snowflake data warehouse. This can lead to faster, data-driven decision-making and a more agile organization.

7. Integration with other data tools #


Metadata management tools can integrate with other data management and analytics tools in your data stack, such as ETL platforms, data visualization tools, and data quality tools. This ensures consistency and accuracy across your entire data ecosystem, maximizing the value of your Snowflake data warehouse.

Overall, deploying a metadata management tool can help you create a more efficient, collaborative, and well-governed data environment, maximizing the value of your Snowflake data warehouse and enabling your organization to make better data-driven decisions.

How organizations making the most out of their data using Atlan #

The recently published Forrester Wave report compared all the major enterprise data catalogs and positioned Atlan as the market leader ahead of all others. The comparison was based on 24 different aspects of cataloging, broadly across the following three criteria:

  1. Automatic cataloging of the entire technology, data, and AI ecosystem
  2. Enabling the data ecosystem AI and automation first
  3. Prioritizing data democratization and self-service

These criteria made Atlan the ideal choice for a major audio content platform, where the data ecosystem was centered around Snowflake. The platform sought a “one-stop shop for governance and discovery,” and Atlan played a crucial role in ensuring their data was “understandable, reliable, high-quality, and discoverable.”

For another organization, Aliaxis, which also uses Snowflake as their core data platform, Atlan served as “a bridge” between various tools and technologies across the data ecosystem. With its organization-wide business glossary, Atlan became the go-to platform for finding, accessing, and using data. It also significantly reduced the time spent by data engineers and analysts on pipeline debugging and troubleshooting.

A key goal of Atlan is to help organizations maximize the use of their data for AI use cases. As generative AI capabilities have advanced in recent years, organizations can now do more with both structured and unstructured data—provided it is discoverable and trustworthy, or in other words, AI-ready.

Tide’s Story of GDPR Compliance: Embedding Privacy into Automated Processes #


  • Tide, a UK-based digital bank with nearly 500,000 small business customers, sought to improve their compliance with GDPR’s Right to Erasure, commonly known as the “Right to be forgotten”.
  • After adopting Atlan as their metadata platform, Tide’s data and legal teams collaborated to define personally identifiable information in order to propagate those definitions and tags across their data estate.
  • Tide used Atlan Playbooks (rule-based bulk automations) to automatically identify, tag, and secure personal data, turning a 50-day manual process into mere hours of work.

Book your personalized demo today to find out how Atlan can help your organization in establishing and scaling data governance programs.

Bringing it all together #

Snowflake’s Information Schema is a set of system-defined views that provide metadata about your account, databases, schemas, tables, and other database objects. In this blog, we discovered a few SQL queries to access this metadata and gather insights about your data warehouse. Besides, we also learnt a few tools to query Snowflake’s Information Schema without writing SQL queries.

By implementing an appropriate metadata management tool and leveraging the Information Schema in Snowflake, you can create a more efficient, collaborative, and well-governed data environment.

Atlan is an active data catalog for the modern data stack and the first cataloging and metadata management solution to be approved by Snowflake Ready Technology Validation Program.

Are you looking to implement a data discovery and data catalog solution for your organization — you might want to check out Atlan.

FAQs about How to Query Information Schema on Snowflake #

1. What is the Information Schema in Snowflake? #


The Information Schema in Snowflake is a collection of system-defined views that provide metadata about your databases, schemas, tables, and other database objects. It allows users to query and retrieve essential information about their data environment.

2. How can I effectively query the Information Schema in Snowflake? #


To effectively query the Information Schema, use SQL commands structured as follows: SELECT * FROM <database_name>.information_schema.<view_name>;. Replace <database_name> and <view_name> with your specific database and the desired view.

3. What SQL commands are commonly used to query the Information Schema in Snowflake? #


Common SQL commands include:

  • SELECT * FROM <database_name>.information_schema.tables; to list all tables.

  • SELECT * FROM <database_name>.information_schema.columns; to list all columns in a specific table.

These commands help retrieve metadata efficiently.

4. How do I find the schema of a table in Snowflake? #


To find the schema of a table in Snowflake, you can query the Information Schema using the command: SELECT * FROM <database_name>.information_schema.columns WHERE table_name = '<table_name>';. This will return details about the columns in the specified table.

5. How can I access user privileges and roles through the Information Schema in Snowflake? #


You can access user privileges and roles by querying the information_schema.role_grants view. Use the command: SELECT * FROM <database_name>.information_schema.role_grants; to retrieve information about user roles and their associated privileges.

6. What are the best practices for querying the Information Schema in Snowflake? #


Best practices include:

  • Using specific views to limit the data returned.

  • Structuring queries to optimize performance.

  • Regularly reviewing the Snowflake documentation for updates on available views and their usage.

Share this article

[Website env: production]