Queries para obter metadados das tabelas do banco

loja_information_schema.sql

-- =====================================================
-- CONSULTAS AO INFORMATION_SCHEMA DO POSTGRESQL
-- Queries para obter metadados das tabelas do banco
-- =====================================================

-- =====================================================
-- 1. LISTAR TODAS AS TABELAS DO BANCO
-- =====================================================
SELECT 
    table_catalog AS database_name,
    table_schema AS schema_name,
    table_name,
    table_type
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;


-- =====================================================
-- 2. TABELAS COM CONTAGEM DE COLUNAS
-- =====================================================
SELECT 
    t.table_name,
    t.table_type,
    COUNT(c.column_name) AS total_colunas
FROM information_schema.tables t
LEFT JOIN information_schema.columns c 
    ON t.table_name = c.table_name 
    AND t.table_schema = c.table_schema
WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema')
GROUP BY t.table_name, t.table_type
ORDER BY t.table_name;


-- =====================================================
-- 3. INFORMAÇÕES DETALHADAS DAS TABELAS
-- =====================================================
SELECT 
    schemaname AS schema_name,
    tablename AS table_name,
    tableowner AS owner,
    tablespace,
    hasindexes AS tem_indices,
    hasrules AS tem_regras,
    hastriggers AS tem_triggers
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY tablename;


-- =====================================================
-- 4. TODAS AS COLUNAS DE TODAS AS TABELAS
-- =====================================================
SELECT 
    table_name,
    column_name,
    ordinal_position AS posicao,
    data_type AS tipo_dado,
    character_maximum_length AS tamanho_max,
    is_nullable AS aceita_null,
    column_default AS valor_padrao
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_name, ordinal_position;


-- =====================================================
-- 5. COLUNAS DE UMA TABELA ESPECÍFICA (EXEMPLO: clientes)
-- =====================================================
SELECT 
    column_name,
    ordinal_position AS posicao,
    data_type AS tipo_dado,
    CASE 
        WHEN character_maximum_length IS NOT NULL 
        THEN character_maximum_length::text
        WHEN numeric_precision IS NOT NULL 
        THEN numeric_precision::text || ',' || numeric_scale::text
        ELSE '-'
    END AS tamanho,
    is_nullable AS aceita_null,
    column_default AS valor_padrao
FROM information_schema.columns
WHERE table_name = 'clientes'
ORDER BY ordinal_position;


-- =====================================================
-- 6. CONSTRAINTS (PRIMARY KEYS, FOREIGN KEYS, ETC)
-- =====================================================
SELECT 
    tc.table_name,
    tc.constraint_name,
    tc.constraint_type,
    kcu.column_name,
    CASE 
        WHEN tc.constraint_type = 'FOREIGN KEY' THEN ccu.table_name
        ELSE NULL
    END AS foreign_table_name,
    CASE 
        WHEN tc.constraint_type = 'FOREIGN KEY' THEN ccu.column_name
        ELSE NULL
    END AS foreign_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
LEFT JOIN information_schema.constraint_column_usage ccu
    ON ccu.constraint_name = tc.constraint_name
    AND ccu.table_schema = tc.table_schema
WHERE tc.table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY tc.table_name, tc.constraint_type, tc.constraint_name;


-- =====================================================
-- 7. APENAS PRIMARY KEYS
-- =====================================================
SELECT 
    tc.table_name,
    kcu.column_name AS primary_key_column,
    tc.constraint_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
    AND tc.table_schema = kcu.table_schema
WHERE tc.constraint_type = 'PRIMARY KEY'
    AND tc.table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY tc.table_name;


-- =====================================================
-- 8. APENAS FOREIGN KEYS
-- =====================================================
SELECT 
    tc.table_name AS tabela_origem,
    kcu.column_name AS coluna_origem,
    ccu.table_name AS tabela_referenciada,
    ccu.column_name AS coluna_referenciada,
    tc.constraint_name AS nome_constraint
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
    AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage ccu
    ON ccu.constraint_name = tc.constraint_name
    AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND tc.table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY tc.table_name, kcu.column_name;


-- =====================================================
-- 9. ÍNDICES DAS TABELAS
-- =====================================================
SELECT 
    schemaname AS schema_name,
    tablename AS table_name,
    indexname AS index_name,
    indexdef AS index_definition
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY tablename, indexname;


-- =====================================================
-- 10. TAMANHO DAS TABELAS EM DISCO
-- =====================================================
SELECT 
    schemaname AS schema_name,
    tablename AS table_name,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS tamanho_total,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS tamanho_tabela,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS tamanho_indices
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;


-- =====================================================
-- 11. ESTATÍSTICAS DE LINHAS POR TABELA
-- =====================================================
SELECT 
    schemaname AS schema_name,
    relname AS table_name,
    n_live_tup AS total_linhas,
    n_dead_tup AS linhas_mortas,
    last_vacuum,
    last_autovacuum,
    last_analyze
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;


-- =====================================================
-- 12. VIEWS DO BANCO
-- =====================================================
SELECT 
    table_schema AS schema_name,
    table_name AS view_name,
    view_definition
FROM information_schema.views
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_name;


-- =====================================================
-- 13. RESUMO COMPLETO: TABELAS COM SUAS COLUNAS
-- =====================================================
SELECT 
    t.table_name,
    t.table_type,
    STRING_AGG(
        c.column_name || ' (' || c.data_type || ')', 
        ', ' 
        ORDER BY c.ordinal_position
    ) AS colunas
FROM information_schema.tables t
JOIN information_schema.columns c 
    ON t.table_name = c.table_name 
    AND t.table_schema = c.table_schema
WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema')
GROUP BY t.table_name, t.table_type
ORDER BY t.table_name;


-- =====================================================
-- 14. VERIFICAR RELACIONAMENTOS ENTRE TABELAS
-- =====================================================
SELECT DISTINCT
    tc1.table_name AS tabela_1,
    tc2.table_name AS tabela_2,
    'relacionadas via FK' AS tipo_relacionamento
FROM information_schema.table_constraints tc1
JOIN information_schema.key_column_usage kcu1
    ON tc1.constraint_name = kcu1.constraint_name
JOIN information_schema.constraint_column_usage ccu
    ON ccu.constraint_name = tc1.constraint_name
JOIN information_schema.table_constraints tc2
    ON ccu.table_name = tc2.table_name
WHERE tc1.constraint_type = 'FOREIGN KEY'
    AND tc1.table_schema NOT IN ('pg_catalog', 'information_schema')
    AND tc1.table_name != tc2.table_name
ORDER BY tc1.table_name, tc2.table_name;


-- =====================================================
-- 15. QUERY COMPLETA: METADADOS ESSENCIAIS
-- =====================================================
SELECT 
    t.table_name AS tabela,
    t.table_type AS tipo,
    (
        SELECT COUNT(*) 
        FROM information_schema.columns c 
        WHERE c.table_name = t.table_name 
        AND c.table_schema = t.table_schema
    ) AS total_colunas,
    (
        SELECT COUNT(*) 
        FROM information_schema.table_constraints tc 
        WHERE tc.table_name = t.table_name 
        AND tc.constraint_type = 'PRIMARY KEY'
    ) AS tem_pk,
    (
        SELECT COUNT(*) 
        FROM information_schema.table_constraints tc 
        WHERE tc.table_name = t.table_name 
        AND tc.constraint_type = 'FOREIGN KEY'
    ) AS total_fks,
    (
        SELECT COUNT(*) 
        FROM pg_indexes i 
        WHERE i.tablename = t.table_name
    ) AS total_indices,
    COALESCE(
        (
            SELECT n_live_tup 
            FROM pg_stat_user_tables s 
            WHERE s.relname = t.table_name
        ), 
        0
    ) AS total_registros
FROM information_schema.tables t
WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY t.table_name;


-- =====================================================
-- FIM DAS QUERIES
-- =====================================================