sqlite recursive common table expression using pragma foreign_key_list

Question

I'm trying to enumerate all parent tables and their parent tables for a given table.

the following sqlite script sets up a parent>child>grandchild hierarchy, does select pragma_foreign_key_list for each table, but I can't get the cte to work.

if this is possible, how do I do it?

PRAGMA foreign_keys = on; 
 
create table parent( 
id INTEGER PRIMARY KEY 
); 
 
create table child 
( 
id INTEGER PRIMARY KEY, 
parentId REFERENCES parent(id) 
); 
 
create table grandChild 
( 
id INTEGER PRIMARY KEY, 
childId REFERENCES child(id) 
); 
 
.mode column 
.header on 
select 'parent' as childTable, "from" as childField, "table" as parentTable, "to" as parentField 
from pragma_foreign_key_list('parent'); 
 
select  'child' as childTable, "from" as childField, "table" as parentTable, "to" as parentField 
from pragma_foreign_key_list('child'); 
 
select '' as ""; 
 
select  'grandChild' as childTable, "from" as childField, "table" as parentTable, "to" as parentField 
from pragma_foreign_key_list('grandChild'); 
 
select '' as ""; 
 
select distinct "table" as tName from pragma_foreign_key_list('grandChild'); 
 
select '' as ""; 
 
with recursive tabs as ( 
select distinct "table" as tName from pragma_foreign_key_list('grandChild') 
union all 
select distinct "table" from pragma_foreign_key_list(quote(select tName from tabs)) 
) 
select * from tabs; 

Answer 1

WITH RECURSIVE CTE AS (
    SELECT 'grandChild' AS `table`
    
    UNION ALL
    
    SELECT fk.`table`
    FROM CTE c, pragma_foreign_key_list(c.`table`) fk
    WHERE fk.`table` <> c.`table`
)
SELECT *
FROM CTE;

or

WITH RECURSIVE CTE AS (
    SELECT 'grandChild' AS `table`, 'grandChild' AS path
    
    UNION ALL
    
    SELECT fk.`table`, path || ' -> ' || fk.`table` AS path
    FROM CTE c, pragma_foreign_key_list(c.`table`) fk
    WHERE fk.`table` <> c.`table`
)
SELECT *
FROM CTE;

EDIT: Add condition. Thanks @sarh