Introspection

INFORMATION_SCHEMA recipes

Q: How do I use INFORMATION_SCHEMA on BigQuery?

BigQuery scopes per region or per dataset. SELECT FROM \region-us\.INFORMATION_SCHEMA.JOBS_BY_PROJECT for region-wide. SELECT FROM \project.dataset\.INFORMATION_SCHEMA.TABLES for one dataset. Always qualify with the region or dataset; unqualified queries fail.

Q: What's a "schema audit" query?

A query against INFORMATION_SCHEMA that surfaces a quality / security signal: tables without primary keys, columns without comments, FK columns without indexes, columns named like ssn / credit_card without encryption. Each is one query; run them in CI on every PR that touches the database.

Q: Can I see view definitions through INFORMATION_SCHEMA?

SELECT view_definition FROM information_schema.views. Returns the SELECT body of each view. On most engines the text is exactly what was provided to CREATE VIEW. Useful for finding views that depend on a particular table — text-search the view bodies.

INFORMATION_SCHEMA is the ANSI-standard set of read-only views that describe every database object: tables, columns, constraints, routines, privileges. Supported on Postgres, MySQL, SQL Server, Snowflake, BigQuery, Oracle (12c+), DuckDB, Redshift. Use it when you need a query that works across engines, or when you want to script schema reports without engine-specific catalog spelunking.

The most useful views #

View	What it tells you
`tables`	every table and view, with type, schema, comment
`columns`	every column, with type, nullability, default, position
`table_constraints`	named constraints (PK, FK, UNIQUE, CHECK)
`key_column_usage`	which columns participate in each constraint
`referential_constraints`	foreign keys with `UPDATE` / `DELETE` rules
`schemata`	every schema
`views`	view definitions (the `SELECT` body)
`routines`	functions and stored procedures
`triggers`	triggers and their event types

Recipes #

sql

-- 1. Every table in your schema with row-count placeholder.
SELECT table_schema, table_name, table_type
FROM   information_schema.tables
WHERE  table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER  BY table_schema, table_name;

-- 2. All columns of a type ("which columns are JSONB?").
SELECT table_schema, table_name, column_name
FROM   information_schema.columns
WHERE  data_type = 'jsonb';

-- 3. Find every column named "email" (typo audit, GDPR mapping).
SELECT table_schema, table_name, column_name, data_type
FROM   information_schema.columns
WHERE  column_name ILIKE '%email%';

-- 4. Tables without a primary key (data quality smell).
SELECT t.table_name
FROM   information_schema.tables t
LEFT  JOIN information_schema.table_constraints c
       ON  t.table_name = c.table_name
       AND c.constraint_type = 'PRIMARY KEY'
WHERE  t.table_type = 'BASE TABLE'
  AND  t.table_schema = 'public'
  AND  c.constraint_name IS NULL;

-- 5. Views that depend on a given table (Postgres / DuckDB).
SELECT view_definition
FROM   information_schema.views
WHERE  view_definition ILIKE '%orders%';
-- Note: text matching, not parse-tree — false positives are possible.

-- 6. Columns added recently (where supported).
SELECT table_name, column_name, last_altered
FROM   information_schema.columns
WHERE  table_schema = 'PUBLIC'
ORDER  BY last_altered DESC
LIMIT  20;                                   -- Snowflake / BigQuery: timestamps are populated; Postgres: not natively.

Example #

Loading SQL editor...

Dialect notes #

Postgres: full ANSI INFORMATION_SCHEMA support. pg_catalog is the underlying engine-specific catalog and is faster + richer; INFORMATION_SCHEMA is the portable veneer.
MySQL / MariaDB: INFORMATION_SCHEMA is the canonical interface (no separate "catalog"). Performance schema is a separate set of views for runtime metrics.
SQL Server: INFORMATION_SCHEMA exists; sys.* views (sys.tables, sys.columns, sys.foreign_keys) are the engine-specific equivalent: more powerful, less portable.
Snowflake: per-database INFORMATION_SCHEMA and account-level SNOWFLAKE.ACCOUNT_USAGE. The latter has retention history (table sizes over time, query history) that INFORMATION_SCHEMA lacks.
BigQuery: must qualify by region/dataset: \region-us\.INFORMATION_SCHEMA.TABLES or \project.dataset\.INFORMATION_SCHEMA.TABLES. Per-region scoping is unique to BigQuery.
Oracle: INFORMATION_SCHEMA is partial (mostly added in 12c). USER_ / ALL_ / DBA_* views are the canonical Oracle catalog.
DuckDB: implements INFORMATION_SCHEMA and supports many Postgres-compatible pg_catalog views. PRAGMA show_tables, PRAGMA table_info are also available.
Redshift: Postgres-compatible INFORMATION_SCHEMA plus Redshift-specific SVV_ and STV_ views for cluster-level data (table size on disk, vacuum stats).

FAQ #

What is INFORMATION_SCHEMA?

A standardized set of read-only views that describe every database object: tables, columns, constraints, routines. ANSI SQL, supported on Postgres, MySQL, SQL Server, Snowflake, BigQuery, Oracle (12c+), DuckDB, Redshift. The portable way to introspect a schema.

Why are INFORMATION_SCHEMA queries slow on huge databases?

These views often scan the full catalog without indexes. On databases with 10K+ tables, queries can take seconds to minutes. Filter aggressively: scope by table_schema = 'specific_schema' and table_name = 'specific_table' whenever possible. Engine-specific catalogs (Postgres pg_catalog, SQL Server sys.*) are usually faster.

How do I use INFORMATION_SCHEMA on BigQuery?

BigQuery scopes per region or per dataset. SELECT FROM \region-us\.INFORMATION_SCHEMA.JOBS_BY_PROJECT for region-wide. SELECT FROM \project.dataset\.INFORMATION_SCHEMA.TABLES for one dataset. Always qualify with the region or dataset; unqualified queries fail.

Is INFORMATION_SCHEMA real-time or cached?

Real-time on most engines — reflects the current catalog state. Some engines lag slightly (Snowflake views can have a few seconds of latency). For real-time runtime data (active queries, locks), use engine-specific views (pg_stat_activity, sys.dm_exec_requests).

What's a "schema audit" query?

A query against INFORMATION_SCHEMA that surfaces a quality / security signal: tables without primary keys, columns without comments, FK columns without indexes, columns named like ssn / credit_card without encryption. Each is one query; run them in CI on every PR that touches the database.

Can I see view definitions through INFORMATION_SCHEMA?

SELECT view_definition FROM information_schema.views. Returns the SELECT body of each view. On most engines the text is exactly what was provided to CREATE VIEW. Useful for finding views that depend on a particular table — text-search the view bodies.

Tip

Build a small library of "schema audit" queries and run them in CI on every PR that touches the database. Tables without primary keys, columns without comments, foreign keys without indexes, columns named like "ssn"/"credit_card" without encryption — these are all one INFORMATION_SCHEMA query each. Run them as a scheduled report (or as a CI check on the migration repo) and the data quality and security posture of your warehouse improves quietly over time. The same query that took 30 minutes to write today saves a quarterly compliance scramble forever.