From table_id to Table Name in YSQL

Not long ago, a Yugabyte Tip shared a neat trick: how to display a table’s table_id in YSQL by calling yb_table_id(oid) inside queries (see the tip here). That’s useful when you’re troubleshooting at the DocDB layer, looking at logs, or mapping internal UUIDs to your SQL schema.

But what if you need to go the other way? In other words, given a table_id string (like the one you might see in master UI or system logs), how do you find out which table it belongs to in YSQL?

The simplest way to map a table ID back to its name is by querying the yb_local_tablets system table (see yet another tip here). The catch is that this only works if the node you’re connected to hosts a tablet (leader or follower) for that table.

Example:

				
					yugabyte=# CREATE TABLE some_table(some_column1 INT PRIMARY KEY, some_column2 TEXT);
CREATE TABLE

yugabyte=# -- User the previous YB Tip to get the table id
yugabyte=# SELECT get_table_id('public', 'some_table');
           get_table_id
----------------------------------
 000034cb00003000800000000000400a
(1 row)

yugabyte=# -- Use yb_local_tablets to get the table name from the table id
yugabyte=# SELECT ysql_schema_name, table_name FROM yb_local_tablets WHERE table_id = '000034cb00003000800000000000400a' LIMIT 1;
 ysql_schema_name | table_name
------------------+------------
 public           | some_table
(1 row)

If you’re running a large cluster or using tablespaces to geo-locate data, there’s a good chance the table’s tablets won’t be local to the node you’re connected to.

The Solution: A Helper Function

Here’s a simple YSQL function that does the reverse: it takes a table_id string and returns the object’s schema, name, type, and even the parent table if it’s an index or partition.

				
					-- Map a Yugabyte table_id (DocDB UUID text) -> object in the *current* database
CREATE OR REPLACE FUNCTION yb_object_from_table_id(p_table_id TEXT)
RETURNS TABLE (
  schema_name  TEXT,
  object_name  TEXT,
  object_type  TEXT,   -- table | partition | index | sequence | other
  relid        OID,
  relfilenode  OID,
  base_table   TEXT    -- for indexes/partitions, the parent table (schema.name)
)
LANGUAGE sql
STABLE
AS $$
WITH norm AS (
  SELECT lower(replace(p_table_id, '-', '')) AS tid
),
candidate AS (
  SELECT
    c.oid,
    c.relkind,
    c.relname,
    c.relfilenode,
    n.nspname,
    ('0000' || lpad(to_hex(d.oid::int), 4, '0')
             || '00003000800000000000'
             || lpad(to_hex(c.relfilenode::int), 4, '0')) AS computed_id
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  CROSS JOIN (SELECT oid FROM pg_database WHERE datname = current_database()) d
),
hit AS (
  SELECT c.*
  FROM candidate c
  JOIN norm ON c.computed_id = norm.tid
),
parent AS (
  SELECT
    h.*,
    CASE
      WHEN h.relkind = 'i' THEN (SELECT indrelid FROM pg_index WHERE indexrelid = h.oid)
      WHEN h.relkind = 'p' THEN (SELECT inhparent FROM pg_inherits WHERE inhrelid  = h.oid LIMIT 1)
      ELSE NULL::oid
    END AS parent_oid
  FROM hit h
)
SELECT
  p.nspname AS schema_name,
  p.relname AS object_name,
  CASE p.relkind
    WHEN 'r' THEN 'table'
    WHEN 'p' THEN 'partition'
    WHEN 'i' THEN 'index'
    WHEN 'S' THEN 'sequence'
    ELSE 'other'
  END           AS object_type,
  p.oid         AS relid,
  p.relfilenode AS relfilenode,
  CASE
    WHEN p.parent_oid IS NULL THEN NULL
    ELSE (
      SELECT pn.nspname || '.' || pc.relname
      FROM pg_class pc
      JOIN pg_namespace pn ON pn.oid = pc.relnamespace
      WHERE pc.oid = p.parent_oid
    )
  END       AS base_table
FROM parent p;
$$;

Example Usage:

				
					yugabyte=# SELECT * FROM yb_object_from_table_id('000034cb00003000800000000000400a');
 schema_name | object_name | object_type | relid | relfilenode | base_table
-------------+-------------+-------------+-------+-------------+------------
 public      | some_table  | table       | 16394 |       16394 |
(1 row)

yugabyte=# CREATE INDEX some_table_some_column2_idx ON some_table(some_column2);
CREATE INDEX

yugabyte=# SELECT * FROM yb_object_from_table_id(get_table_id('public', 'some_table_some_column2_idx'));
 schema_name |         object_name         | object_type | relid | relfilenode |    base_table
-------------+-----------------------------+-------------+-------+-------------+-------------------
 public      | some_table_some_column2_idx | index       | 16399 |       16399 | public.some_table
(1 row)

Why It’s Useful

• Troubleshooting: You can map DocDB errors back to the exact table in YSQL.
• Schema insight: You’ll know if a table_id belongs to a base table, a partition, an index, or even a sequence.
• Symmetry: Now you can go both directions… table → ID and ID → table.

Have Fun!