SQLite list ALL foreign keys in a database
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.
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;