— 1. 查询所有表及其大小
SELECT
c.relname as table_name,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_size,
pg_size_pretty(pg_relation_size(c.oid)) as table_size,
pg_size_pretty(pg_indexes_size(c.oid)) as index_size,
c.reltuples::bigint as row_count
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = ‘r’
AND n.nspname = ‘public’
ORDER BY pg_total_relation_size(c.oid) DESC;

— 2. 查询所有索引
SELECT
schemaname,
tablename,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_indexes
WHERE schemaname = ‘public’
ORDER BY pg_relation_size(indexrelid) DESC;

— 3. 查询表的所有列详细信息
SELECT
a.attname as column_name,
t.typname as data_type,
a.attnotnull as not_null,
pg_get_expr(d.adbin, d.adrelid) as default_value,
CASE WHEN pk.attname IS NOT NULL THEN ‘PK’ ELSE ” END as is_primary_key
FROM pg_attribute a
JOIN pg_type t ON a.atttypid = t.oid
LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
LEFT JOIN (
SELECT a.attname
FROM pg_index i
JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
WHERE i.indrelid = ‘fgedu_employees’::regclass AND i.indisprimary
) pk ON a.attname = pk.attname
WHERE a.attrelid = ‘fgedu_employees’::regclass
AND a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum;

— 4. 查询外键关系
SELECT
tc.conname as constraint_name,
tc.conrelid::regclass as table_name,
kcu.column_name,
ccu.table_name as foreign_table_name,
ccu.column_name as foreign_column_name
FROM pg_constraint tc
JOIN information_schema.key_column_usage kcu
ON tc.conname = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
ON ccu.constraint_name = tc.conname
WHERE tc.contype = ‘f’;

— 1. 查询数据库中所有表
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. 查询表的所有列
SELECT
column_name,
data_type,
character_maximum_length,
numeric_precision,
numeric_scale,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_schema = ‘public’
AND table_name = ‘fgedu_employees’
ORDER BY ordinal_position;

— 3. 查询所有主键
SELECT
tc.table_name,
kcu.column_name,
tc.constraint_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = ‘PRIMARY KEY’
AND tc.table_schema = ‘public’
ORDER BY tc.table_name;

— 4. 查询所有外键
SELECT
tc.table_name,
kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name,
tc.constraint_name
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 ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = ‘FOREIGN KEY’
AND tc.table_schema = ‘public’
ORDER BY tc.table_name;

— 1. 获取完整的表信息（结合两者）
SELECT
t.table_name,
t.table_type,
(SELECT COUNT(*) FROM information_schema.columns c
WHERE c.table_name = t.table_name) as column_count,
(SELECT COUNT(*) FROM information_schema.table_constraints tc
WHERE tc.table_name = t.table_name AND tc.constraint_type = ‘PRIMARY KEY’) as has_primary_key,
pg_size_pretty(pg_total_relation_size(t.table_schema || ‘.’ || t.table_name)) as total_size,
(SELECT reltuples::bigint FROM pg_class WHERE relname = t.table_name) as row_count
FROM information_schema.tables t
WHERE t.table_schema = ‘public’
AND t.table_type = ‘BASE TABLE’
ORDER BY pg_total_relation_size(t.table_schema || ‘.’ || t.table_name) DESC;

— 2. 生成表结构文档（结合两者）
SELECT
c.table_name,
c.column_name,
c.data_type ||
CASE
WHEN c.character_maximum_length IS NOT NULL
THEN ‘(‘ || c.character_maximum_length || ‘)’
ELSE ”
END as data_type,
CASE WHEN c.is_nullable = ‘NO’ THEN ‘NOT NULL’ ELSE ” END as nullable,
COALESCE(c.column_default, ”) as default_value,
COALESCE(
(SELECT ‘PK’ FROM pg_index i
JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
WHERE i.indrelid = (c.table_schema || ‘.’ || c.table_name)::regclass
AND i.indisprimary AND a.attname = c.column_name
LIMIT 1),
”
) as key_type
FROM information_schema.columns c
WHERE c.table_schema = ‘public’
ORDER BY c.table_name, c.ordinal_position;