Metadata Introspection via Information Schema

Snippet

Metadata Introspection via Information Schema

Expert SQL developers use the INFORMATION_SCHEMA to write database-agnostic scripts for auditing or dynamic query generation. This snippet queries the standard-compliant metadata views to retrieve the exact structure of a specific table.

snippet.sql
sql
SELECT 
    table_name, 
    column_name, 
    data_type, 
    is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'employees'
ORDER BY ordinal_position;

Breakdown

FROM information_schema.columns

Accesses the ANSI-standard view containing information about all columns in the database.

WHERE table_schema = 'public'

Filters by the specific schema to avoid metadata from system tables.

Previous snippet Next snippet

More Expert SQL exercises →

From your library

Metadata Introspection via Information Schema

Related