Trouver les noms de colonnes SQLite dans une table vide

Question

Pour commencer, je suis en train d'écrire un outil de "documentation de schéma" qui génère une description des tables et des relations dans une base de données. Je suis en train de le modifier pour qu'il fonctionne avec SQLite.

J'ai réussi à extraire les noms de toutes les tables d'une base de données SQLite via une requête sur le fichier sqlite_master table. Pour chaque nom de table, je lance alors un simple

select * from <table name>

puis utilisez la fonction sqlite3_column_count() et sqlite3_column_name() pour collecter les noms de colonnes, que j'envoie ensuite à l'application sqlite3_table_column_metadata() pour obtenir des informations supplémentaires. C'est assez simple, non ?

Le problème est que cela ne fonctionne que pour les tables qui ne sont pas vides. C'est-à-dire que les sqlite_column_*() Les API ne sont valables que si sqlite_step() est revenu SQLITE_ROW ce qui n'est pas le cas pour les tables vides.

La question est donc la suivante : comment puis-je découvrir les noms de colonnes pour les tables vides ? Ou, plus généralement, y a-t-il un meilleur moyen d'obtenir ce type d'informations sur le schéma en SQLite ?

J'ai l'impression qu'il doit y avoir un autre caché sqlite_xxx table se cachant quelque part contenant cette information, mais jusqu'à présent je n'ai pas pu la trouver.

Answer 1

SchemaCrawler est un outil gratuit de documentation de schémas, et supporte SQLite.

Answer 2

Si vous utilisez SQLite 3.8.3 ou une version ultérieure (supportant la clause WITH), cette requête récursive devrait fonctionner pour les tables de base. Sur CTAS, YMMV.

WITH
    Recordify(tbl_name, Ordinal, Clause, Sql)
AS
    (
     SELECT
        tbl_name,
        0,

        '',
        Sql
     FROM
        (
         SELECT
            tbl_name,
            substr
            (
             Sql,
             instr(Sql, '(') + 1,
             length(Sql) - instr(Sql, '(') - 1
            ) || ',' Sql
         FROM
            sqlite_master
         WHERE
            type = 'table'
        )
     UNION ALL
     SELECT
        tbl_name,
        Ordinal + 1,
        trim(substr(Sql, 1, instr(Sql, ',') - 1)),
        substr(Sql, instr(Sql, ',') + 1)
     FROM
        Recordify
     WHERE
        Sql > ''
       AND  lower(trim(Sql)) NOT LIKE 'check%'
       AND  lower(trim(Sql)) NOT LIKE 'unique%'
       AND  lower(trim(Sql)) NOT LIKE 'primary%'
       AND  lower(trim(Sql)) NOT LIKE 'foreign%'
       AND  lower(trim(Sql)) NOT LIKE 'constraint%'
    ),
    -- Added to make querying a subset easier.
    Listing(tbl_name, Ordinal, Name, Constraints)
AS
    (
     SELECT
        tbl_name,
        Ordinal,
        substr(Clause, 1, instr(Clause, ' ') - 1),
        trim(substr(Clause, instr(Clause, ' ') + 1))
     FROM
        Recordify
     WHERE
        Ordinal > 0
    )
SELECT
    tbl_name,
    Ordinal,
    Name,
    Constraints
FROM
    Listing
ORDER BY
    tbl_name,
    lower(Name);