Querying the system catalog - Amazon Quantum Ledger Database (Amazon QLDB)

Querying the system catalog

Each table that you create in an Amazon QLDB ledger has a system-assigned unique ID. You can find a table's ID, its list of indexes, and other metadata by querying the system catalog table information_schema.user_tables.

All QLDB-assigned IDs are universally unique identifiers (UUID) that are each represented in a Base62-encoded string. For more information, see Unique IDs in Amazon QLDB.

The following example shows the results of a query that returns metadata attributes of the VehicleRegistration table.

SELECT * FROM information_schema.user_tables WHERE name = 'VehicleRegistration'
{ tableId: "5PLf9SXwndd63lPaSIa0O6", name: "VehicleRegistration", indexes: [ { indexId: "Djg2nt0yIs2GY0T29Kud1z", expr: "[VIN]", status: "ONLINE" }, { indexId: "4tPW3fUhaVhDinRgKRLhGU", expr: "[LicensePlateNumber]", status: "BUILDING" } ], status: "ACTIVE" }

Table metadata fields

  • tableId – The unique ID of the table.

  • name – The table name.

  • indexes – The list of indexes on the table.

    • indexId – The unique ID of the index.

    • expr – The indexed document path. This field is a string in the form: [fieldName].

    • status – The index's current status (BUILDING, FINALIZING, ONLINE, FAILED, or DELETING). QLDB does not use the index in queries until the status is ONLINE.

    • message – The error message that describes the reason that the index has a FAILED status. This field is only included for failed indexes.

  • status – The table's current status (ACTIVE or INACTIVE). A table becomes INACTIVE when you DROP it.

To learn how to manage tables using the DROP TABLE and UNDROP TABLE statements, proceed to Managing tables.