How to list tables in PostgreSQL

-- List tables in current schema
\dt

-- List tables in a specific schema
\dt schema_name.*

-- List tables matching a pattern
\dt *users*

-- List tables with additional details (size, description)
\dt+

-- List all tables in all schemas
\dt *.*

-- Include system tables
\dt *.*.*

-- List all tables in current schema
SELECT table_name
FROM information_schema.tables
WHERE table_schema = current_schema()
AND table_type = 'BASE TABLE';

-- List all tables in all schemas
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;

-- List tables with column information
SELECT t.table_schema, t.table_name, c.column_name, c.data_type
FROM information_schema.tables t
JOIN information_schema.columns c
  ON t.table_schema = c.table_schema
  AND t.table_name = c.table_name
WHERE t.table_schema = 'public'
AND t.table_type = 'BASE TABLE'
ORDER BY t.table_name, c.ordinal_position;

-- Find tables with specific naming patterns
SELECT table_name
FROM information_schema.tables
WHERE table_name LIKE '%user%'
AND table_schema = 'public'
AND table_type = 'BASE TABLE';

-- Find tables created after a specific date
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;

-- List all user tables
SELECT schemaname, tablename
FROM pg_catalog.pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

-- List tables with their sizes
SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC;

-- Approximate row counts (fast)
SELECT
    schemaname,
    relname AS table_name,
    n_live_tup AS row_count
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

-- Exact row counts (slow for large tables)
SELECT
    schemaname AS schema_name,
    relname AS table_name,
    (SELECT count(*) FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     WHERE c.relname = relname AND n.nspname = schemaname) AS row_count
FROM pg_stat_user_tables
ORDER BY schemaname, relname;

SELECT
    schemaname AS schema_name,
    relname AS table_name,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY relname;

-- List tables and their indexes
SELECT
    t.schemaname AS table_schema,
    t.relname AS table_name,
    i.relname AS index_name,
    a.attname AS column_name
FROM pg_stat_user_tables t
JOIN pg_index ix ON t.relid = ix.indrelid
JOIN pg_class i ON ix.indexrelid = i.oid
JOIN pg_attribute a ON t.relid = a.attrelid AND a.attnum = ANY(ix.indkey)
ORDER BY t.relname, i.relname, a.attnum;

-- Function to show tables with row counts
CREATE OR REPLACE FUNCTION show_tables_with_counts()
RETURNS TABLE (
    schema_name text,
    table_name text,
    row_count bigint
) AS $$
BEGIN
    FOR schema_name, table_name IN
        SELECT schemaname, tablename FROM pg_tables
        WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
    LOOP
        RETURN QUERY EXECUTE format(
            'SELECT %L::text, %L::text, count(*)::bigint FROM %I.%I',
            schema_name, table_name, schema_name, table_name
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Usage:
-- SELECT * FROM show_tables_with_counts();

-- Use this instead of pg_tables
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

-- Check current database
SELECT current_database();

-- Check search path
SHOW search_path;

-- Set search path
SET search_path TO my_schema, public;