SQL stuff

Clueless, 1995

As if!

table schema

WITH columns_info AS (
    SELECT 
        m.name AS table_name,
        p.cid AS column_id,
        p.name AS column_name,
        p.type AS column_type,
        CASE 
            WHEN p.pk = 1 THEN '✅'
            ELSE '-'
        END AS primary_key,
        CASE 
            WHEN p."notnull" = 1 THEN '✅'
            ELSE '-'
        END AS not_null,
        CASE
            WHEN instr(lower(m.sql), 'unique(' || lower(p.name) || ')') > 0 
              OR instr(lower(m.sql), 'unique (' || lower(p.name) || ')') > 0
              OR instr(lower(m.sql), 'UNIQUE (' || lower(p.name) || ',') > 0
              OR instr(lower(m.sql), ', ' || lower(p.name) || ')') > 0
              OR exists (
                  SELECT 1 
                  FROM pragma_index_list(m.name) idx
                  JOIN pragma_index_info(idx.name) ixinfo
                  WHERE ixinfo.name = p.name AND idx."unique" = 1
              )
            THEN '✅'
            ELSE '-'
        END AS unique_constraint,
        CASE 
            WHEN p.dflt_value IS NULL THEN '-'
            WHEN p.dflt_value = 'NULL' THEN 'NULL'
            ELSE p.dflt_value
        END AS default_value,
        COALESCE(fk."table", '-') AS referenced_table,
        COALESCE(fk."to", '-') AS referenced_column,
        COALESCE(fk."on_delete", '-') AS on_delete_action,
        COALESCE(fk."on_update", '-') AS on_update_action,
        CASE 
            WHEN instr(lower(m.sql), 'check (' || lower(p.name)) > 0 
                 AND instr(substr(m.sql, instr(lower(m.sql), 'check (')), ')') > 0 THEN 
                substr(
                    m.sql, 
                    instr(lower(m.sql), 'check (' || lower(p.name)), 
                    instr(substr(m.sql, instr(lower(m.sql), 'check (' || lower(p.name))), ')') 
                    + instr(lower(m.sql), 'check (' || lower(p.name) || ')')
                )
            ELSE '-'
        END AS check_constraint,
        CASE
            WHEN row_number() OVER (PARTITION BY m.name ORDER BY p.cid) = 1 THEN m.sql
            ELSE '-'
        END AS create_table_query
    FROM 
        sqlite_master AS m
    JOIN 
        pragma_table_info(m.name) AS p
    LEFT JOIN 
        pragma_foreign_key_list(m.name) AS fk 
        ON p.name = fk."from"
    WHERE 
        m.type = 'table'
)
SELECT
    table_name,
    column_name,
    column_type,
    primary_key,
    not_null,
    unique_constraint,
    default_value,
    referenced_table,
    referenced_column,
    on_delete_action,
    on_update_action,
    check_constraint,
    create_table_query
FROM 
    columns_info

UNION ALL

SELECT
    CASE
        WHEN table_name IS NULL THEN '………………………………………………………'
        ELSE table_name || '………………………………………………………………………'
    END AS table_name,
    '………………………………………………………………………' AS column_name,
    '………………………………………………………' AS column_type,
    '…………………………………………………………………' AS primary_key,
    '…………………………………………………………………' AS not_null,
    '…………………………………………………………………' AS unique_constraint,
    '…………………………………………………………………' AS default_value,
    '…………………………………………………………………' AS referenced_table,
    '……………………………………………………………………………………' AS referenced_column,
    '…………………………………………………………………' AS on_delete_action,
    '………………………………………………………………………………………' AS on_update_action,
    '………………………………………………………………………………………………………………………………………………' AS check_constraint,
    '…………………………………………………………………………………………………………………………………………………………' AS create_table_query
FROM (
    SELECT DISTINCT table_name 
    FROM columns_info
) AS t
ORDER BY 
    table_name,
    column_name;