05. Schema Query Reference

SELECT name FROM sqlite_master
WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
ORDER BY name;

SHOW TABLES;

SELECT tablename FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;

SELECT name, sql FROM sqlite_master
WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
ORDER BY name;

SHOW CREATE TABLE orders;

# From terminal
pg_dump -U postgres -t orders --schema-only ecommerce

SELECT pg_get_tabledef('orders'::regclass);

PRAGMA table_info('orders');

DESCRIBE orders;

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders'
ORDER BY ORDINAL_POSITION;

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

SELECT name FROM sqlite_master
WHERE type = 'view'
ORDER BY name;

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = DATABASE();

-- Regular views
SELECT viewname FROM pg_views
WHERE schemaname = 'public'
ORDER BY viewname;

-- Materialized Views
SELECT matviewname FROM pg_matviews
WHERE schemaname = 'public'
ORDER BY matviewname;

SELECT name, sql FROM sqlite_master
WHERE type = 'view' AND name = 'v_monthly_sales';

SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'v_monthly_sales';

SELECT definition FROM pg_views
WHERE viewname = 'v_monthly_sales';

SELECT name, tbl_name
FROM sqlite_master
WHERE type = 'index'
ORDER BY tbl_name, name;

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

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

PRAGMA index_list('orders');

SHOW INDEX FROM orders;

SELECT indexname, indexdef FROM pg_indexes
WHERE tablename = 'orders';

SELECT name, tbl_name FROM sqlite_master
WHERE type = 'trigger'
ORDER BY name;

SELECT TRIGGER_NAME, EVENT_OBJECT_TABLE,
       EVENT_MANIPULATION, ACTION_TIMING
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE();

SELECT tgname AS trigger_name,
       relname AS table_name,
       proname AS function_name
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
JOIN pg_proc p ON t.tgfoid = p.oid
WHERE NOT t.tgisinternal
ORDER BY tgname;

SELECT sql FROM sqlite_master
WHERE type = 'trigger' AND name = 'trg_orders_updated_at';

SHOW CREATE TRIGGER trg_orders_updated_at;

SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = DATABASE()
ORDER BY ROUTINE_TYPE, ROUTINE_NAME;

SELECT routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema = 'public'
ORDER BY routine_type, routine_name;

PRAGMA foreign_key_list('orders');

SELECT TABLE_NAME, COLUMN_NAME,
       REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME;

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

-- Table comment
ALTER TABLE orders COMMENT = 'Orders table';

-- Column comment (must include column definition)
ALTER TABLE orders
MODIFY COLUMN status ENUM('pending','paid','preparing','shipped',
    'delivered','confirmed','cancelled','return_requested','returned')
    NOT NULL COMMENT 'Order status';

-- Table comment
COMMENT ON TABLE orders IS 'Orders table';

-- Column comments
COMMENT ON COLUMN orders.status IS 'Order status';
COMMENT ON COLUMN orders.total_amount IS 'Order total amount (KRW)';
COMMENT ON COLUMN customers.grade IS 'Customer grade: BRONZE/SILVER/GOLD/VIP';

-- Table comments
SELECT TABLE_NAME, TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_COMMENT != ''
ORDER BY TABLE_NAME;

-- Column comments
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND COLUMN_COMMENT != ''
ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- Table comments
SELECT c.relname AS table_name,
       d.description AS comment
FROM pg_class c
JOIN pg_description d ON c.oid = d.objoid AND d.objsubid = 0
WHERE c.relkind = 'r'
ORDER BY c.relname;

-- Column comments
SELECT c.relname AS table_name,
       a.attname AS column_name,
       d.description AS comment
FROM pg_class c
JOIN pg_attribute a ON c.oid = a.attrelid
JOIN pg_description d ON c.oid = d.objoid AND a.attnum = d.objsubid
WHERE c.relkind = 'r' AND a.attnum > 0
ORDER BY c.relname, a.attnum;

SELECT 'customers' AS tbl, COUNT(*) AS cnt FROM customers
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'products', COUNT(*) FROM products
UNION ALL
SELECT 'reviews', COUNT(*) FROM reviews
UNION ALL
SELECT 'payments', COUNT(*) FROM payments
ORDER BY cnt DESC;

SELECT TABLE_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_ROWS DESC;

SELECT relname AS table_name,
       n_live_tup AS estimated_rows
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

SELECT COUNT(*) FROM orders;