SQLite list ALL foreign keys in a database

Is there a way of listing ALL foreign keys in a SQLite database?

They don't seem to be stored in sqlite_master and PRAGMA foreign_key_list('table') only lists one at a time.

Alternatively, is t开发者_如何学Chere a way of listing what foreign keys reference a table?

It seems that all (or many) of the PRAGMA commands can be programatically selected with a little trick;

Usually the are called like:

PRAGMA table_info('my_table');
PRAGMA foreign_key_list('my_table');

But this can also be done:

SELECT * FROM pragma_table_info('my_table');
SELECT * FROM pragma_foreign_key_list('my_table');

And the schema can also be (more or less) obtained:

.schema pragma_table_info
/* pragma_table_info(cid,name,type,"notnull",dflt_value,pk) */;

.schema pragma_foreign_key_list
/* pragma_foreign_key_list(id,seq,"table","from","to",on_update,on_delete,"match") */

So, to get all the fks a JOIN between sqlite_master and pragma_foreign_key_list can do the trick:

SELECT 
    m.name
    , p.*
FROM
    sqlite_master m
    JOIN pragma_foreign_key_list(m.name) p ON m.name != p."table"
WHERE m.type = 'table'
ORDER BY m.name
;

Just take care, that some fields of pragma_foreign_key_list like table, from, ... must be quoted;

With the SQLite shell, use the .schema instruction, and use GREP to filter lines containing REFERENCES.

From shell.c in the SQLite repository, today's version in the trunk, two queries:

SELECT sql
  FROM (
        SELECT sql sql, type type, tbl_name tbl_name, name name
          FROM sqlite_master
         UNION ALL
        SELECT sql, type, tbl_name, name
          FROM sqlite_temp_master
       )
 WHERE tbl_name LIKE shellstatic()
   AND type != 'meta'
   AND sql NOTNULL
 ORDER BY substr(type, 2, 1), name

and

SELECT sql
  FROM (
        SELECT sql sql, type type, tbl_name tbl_name, name name
          FROM sqlite_master
         UNION ALL
        SELECT sql, type, tbl_name, name
          FROM sqlite_temp_master
       )
 WHERE type != 'meta'
   AND sql NOTNULL
   AND name NOT LIKE 'sqlite_%'
 ORDER BY substr(type, 2, 1), name

The second one is probably what you are looking for.