❄️ DESCRIBE Command in Snowflake

Complete guide to using DESCRIBE in Snowflake SQL

Basic DESCRIBE Syntax

-- Describe a table
DESCRIBE TABLE table_name;
DESC TABLE table_name;

-- Describe a view
DESCRIBE VIEW view_name;

-- Describe a schema
DESCRIBE SCHEMA schema_name;

-- Describe a stage
DESCRIBE STAGE stage_name;

What is DESCRIBE in Snowflake?

The DESCRIBE command (or its alias DESC) in Snowflake displays detailed metadata about database objects. It's essential for understanding table structures, column data types, constraints, and other object properties without querying the data itself.

DESCRIBE TABLE - Most Common Usage

The most frequent use of DESCRIBE is to view table structure:

SQL
DESCRIBE TABLE customers;

-- Output columns:
-- name: Column name
-- type: Data type (VARCHAR, NUMBER, DATE, etc.)
-- kind: COLUMN
-- null?: Y or N (nullable or not)
-- default: Default value
-- primary key: PK if primary key
-- unique key: UK if unique key
-- check: Check constraint
-- expression: For computed columns
-- comment: Column comment

DESCRIBE TABLE Example Output

name type kind null? default primary key
CUSTOMER_ID NUMBER(38,0) COLUMN N NULL PK
FIRST_NAME VARCHAR(100) COLUMN N NULL
EMAIL VARCHAR(255) COLUMN Y NULL
CREATED_AT TIMESTAMP_NTZ(9) COLUMN N CURRENT_TIMESTAMP()

DESCRIBE vs SHOW Commands

Command Purpose Example
DESCRIBE TABLE Show structure of one table DESC TABLE customers;
SHOW TABLES List all tables in schema SHOW TABLES;
DESCRIBE VIEW Show structure of one view DESC VIEW sales_view;
SHOW VIEWS List all views in schema SHOW VIEWS;

DESCRIBE Object Types

All DESCRIBE Variants
-- Tables and Views
DESCRIBE TABLE my_table;
DESCRIBE VIEW my_view;
DESCRIBE EXTERNAL TABLE my_external_table;
DESCRIBE MATERIALIZED VIEW my_mat_view;

-- Schemas and Databases
DESCRIBE SCHEMA my_schema;
DESCRIBE DATABASE my_database;

-- Stages and File Formats
DESCRIBE STAGE my_stage;
DESCRIBE FILE FORMAT my_format;

-- Functions and Procedures
DESCRIBE FUNCTION my_function(NUMBER, VARCHAR);
DESCRIBE PROCEDURE my_proc(NUMBER);

-- Sequences and Streams
DESCRIBE SEQUENCE my_sequence;
DESCRIBE STREAM my_stream;

Practical Examples

Example 1: Check Column Data Types
-- Before inserting data, verify column types
DESCRIBE TABLE orders;

-- Check if column accepts NULL
-- Look for 'null?' column in output
Example 2: Find Primary Keys
-- Identify primary key columns
DESCRIBE TABLE customers;

-- Look for 'PK' in 'primary key' column
Example 3: View Default Values
-- See which columns have default values
DESCRIBE TABLE events;

-- Check 'default' column for CURRENT_TIMESTAMP(), etc.

DESCRIBE with Fully Qualified Names

-- Use fully qualified names for clarity
DESCRIBE TABLE database_name.schema_name.table_name;

-- Or set context first
USE DATABASE my_database;
USE SCHEMA my_schema;
DESCRIBE TABLE my_table;

Common Use Cases

DESCRIBE Output in Scripts

Query DESCRIBE Results
-- Store DESCRIBE output in a table
CREATE OR REPLACE TABLE table_metadata AS
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "name" = 'DESCRIBE TABLE my_table';

-- Filter DESCRIBE results
DESCRIBE TABLE customers;
-- Then query specific columns from result set

Best Practices

DESCRIBE vs Information Schema

For programmatic access to metadata, use INFORMATION_SCHEMA views:

-- Alternative to DESCRIBE TABLE
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'CUSTOMERS'
  AND table_schema = 'PUBLIC';

-- More flexible for filtering and joining

🔗 Related Snowflake ID Tools

🐦 Twitter Snowflake Decoder 💬 Discord Snowflake Decoder 📸 Instagram Snowflake Decoder

📚 Learn More About Snowflake IDs

How to Decode Tutorial Real ID Examples Snowflake Calculator ID to Timestamp