Bird
Raised Fist0
SnowflakeConceptBeginner · 3 min read

What is information_schema in Snowflake: Overview and Usage

In Snowflake, information_schema is a built-in schema that stores metadata about your database objects like tables, views, and columns. It lets you query details about your database structure using standard SQL queries.
⚙️

How It Works

Think of information_schema as a special catalog inside your Snowflake database that holds information about all the objects you have, such as tables, columns, and views. Instead of looking inside each table, you can ask this catalog questions about your database setup.

It works like a directory or an index in a library. If you want to find all the books (tables) about a topic or see the details of a specific book (columns in a table), you check the directory instead of searching every shelf. This makes it easy to understand your database layout without touching the actual data.

💻

Example

This example shows how to list all tables in your current database using information_schema:
sql
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'PUBLIC';
Output
TABLE_NAME | TABLE_TYPE -------------|----------- EMPLOYEES | BASE TABLE DEPARTMENTS | BASE TABLE SALES_VIEW | VIEW
🎯

When to Use

Use information_schema when you want to explore or document your database structure without accessing the actual data. It helps developers and administrators understand what tables, columns, or views exist and their properties.

For example, before writing queries, you might check which columns a table has or find all views in a schema. It is also useful for automated scripts that monitor or audit database objects.

✅

Key Points

  • Standardized: information_schema follows SQL standards, so it works similarly across many databases.
  • Read-only: You can only query metadata; you cannot change it here.
  • Accessible: Available in every Snowflake database by default.
  • Helpful for automation: Useful for scripts that manage or report on database objects.
✅

Key Takeaways

information_schema provides metadata about database objects in Snowflake.
It allows you to query details like tables, columns, and views using SQL.
Use it to explore, document, or automate database structure tasks.
It is read-only and follows SQL standards for metadata access.