show-schema-tree.md

$ export PROJECT_ID=gcpug-public-spanner INSTANCE_ID=merpay-sponsored-instance DATABASE_ID=apstndb-sampledb-with-data-idx
$ sh show-schema-tree.sh
Concerts
Singers
  Albums
    Songs
    SongsBySingerAlbumSongNameDesc on Songs
SongGenres
AlbumsByAlbumTitle on Albums
AlbumsByAlbumTitle2 on Albums
ConcertsBySingerId on Concerts
IDX_Concerts_SingerId_B428E23F69F5F316 on Concerts
SingersByFirstLastName on Singers
SingersByFirstLastNameStoring on Singers
SongsBySongGenre on Songs
SongsBySongGenreSongName on Songs
SongsBySongGenreStoring on Songs
SongsBySongName on Songs

show-schema-tree.sh

gcloud spanner databases execute-sql --project=${PROJECT_ID} --instance=${INSTANCE_ID} ${DATABASE_ID} --format=json --sql='
SELECT "TABLE" AS TYPE, TABLE_NAME, PARENT_TABLE_NAME, "" AS INDEX_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_CATALOG, TABLE_SCHEMA) = ("", "")
UNION ALL
SELECT "INDEX" AS TYPE, TABLE_NAME, PARENT_TABLE_NAME, INDEX_NAME
FROM INFORMATION_SCHEMA.INDEXES
WHERE (TABLE_CATALOG, TABLE_SCHEMA) = ("", "") AND INDEX_NAME != "PRIMARY_KEY"
ORDER BY TYPE DESC, TABLE_NAME, INDEX_NAME
' | jq -r '
def resultset_to_maps: .metadata.rowType.fields as $fields | .rows | map(with_entries(.key |= $fields[.].name));

(. | resultset_to_maps) as $tables |
$tables[] |
select((.PARENT_TABLE_NAME? // "") == "") |
recurse(
        (.depth? // 0) as $depth |
        .TABLE_NAME as $parent |
        if .TYPE == "INDEX" then empty else . end |
        $tables[] |
        select(.PARENT_TABLE_NAME == $parent) |
        .depth = ($depth + 1)
       ) |
"\("  " * (.depth // 0) // "")\(if .TYPE == "INDEX" then "\(.INDEX_NAME) on \(.TABLE_NAME)" else .TABLE_NAME end)"
'