query-database-schema

Query Database Schema

Quick Start

Use this skill to quickly answer: what tables exist, what columns/types they have, how tables relate, and what constraints/indexes affect query behavior.

When to use this skill

• onboarding onto an unfamiliar database
• before writing joins, aggregations, or migrations
• when app behavior suggests bad assumptions (nullability, defaults, constraints)
• when documenting schema for teammates

Before running queries, collect:

• database engine (postgres/mysql/sqlite)
• database + schema name (if applicable)
• target tables (or suspected domain area)

Common Database Queries

PostgreSQL

list schemas:

SELECT schema_name
FROM information_schema.schemata
ORDER BY schema_name;

List all tables:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';

Get table structure:

SELECT
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_name = 'your_table_name'
ORDER BY ordinal_position;

list indexes (including uniqueness):

SELECT
  tablename,
  indexname,
  indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'your_table_name'
ORDER BY indexname;

list constraints (pk/unique/check/fk):

SELECT
  tc.constraint_type,
  tc.constraint_name,
  kcu.column_name
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
  AND tc.table_schema = kcu.table_schema
WHERE tc.table_schema = 'public'
  AND tc.table_name = 'your_table_name'
ORDER BY tc.constraint_type, tc.constraint_name, kcu.ordinal_position;

Find foreign key relationships:

SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';