Is it possible to query all columns within a table that have references to other tables in sqlite?

Question

Pretty much as the title says, if I have a table, created like this:

CREATE TABLE "MyTable" ( 
"ID" INTEGER PRIMARY KEY NOT NULL, 
"OtherTable1ID" INTEGER NOT NULL CONSTRAINT 'OtherTable1ID' REFERENCES 'OtherTable1' ('ID'), 
"OtherTable2ID" INTEGER NOT NULL CONSTRAINT 'OtherTable2ID' REFERENCES 'OtherTable2' ('ID'), 
"Repetition" INTEGER
)

I'm looking for a query, that returns me the column names OtherTable1IDand OtherTable2ID.

My own approach would be to query the table's create statement and look for the REFERENCES keyword (with Regex), but I was hoping there is a more simple solution?

Second approach is to get all column names within the table, and look if they contain 'ID' in the name. This works mostly, since the databases in this case all follow this naming convention, however what if a table holds a column which name contains 'ID' but doesn't reference anything? Therefore I need a generally applicable solution.

I think this link may help you https://stackoverflow.com/questions/1054984/how-can-i-get-column-names-from-a-table-in-sql-server — amal mansour, Dec 17 '18 at 13:49
Look into the [foreign_key_list](https://www.sqlite.org/pragma.html#pragma_foreign_key_list) pragma. — Shawn, Dec 17 '18 at 16:06
@Shawn thank you so much, this is exactly what I was looking for. If you put it as answer, I will mark it. — Roland Deschain, Dec 17 '18 at 16:32

score 1 · Accepted Answer · answered Dec 17 '18 at 16:49

You can list and get information about a table's foreign key constraints with the foreign_key_list pragma:

PRAGMA foreign_key_list(MyTable);

If you just want the names of columns and don't care about the rest of the information (or want to use them in a bigger query):

SELECT "from" FROM pragma_foreign_key_list('MyTable');

Is it possible to query all columns within a table that have references to other tables in sqlite?

1 Answers1