Querying the system catalog

Important

End of support notice: Existing customers will be able to use Amazon QLDB until end of support on 07/31/2025. For more details, see Migrate an Amazon QLDB Ledger to Amazon Aurora PostgreSQL.

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 system-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 doesn't 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.