We can display a list of tables and their UUID (table_id) values by opening the YB-Master UI (<master_host>:7000/) and then clicking Tables in the navigation bar.
Example:
We can also display the table_id using the following SQL statement…
SELECT n.nspname "Schema Name",
c.oid "YSQL OID",
'0000' || lpad(to_hex(d.oid::int), 4, '0') || '00003000800000000000' || lpad(to_hex(c.relfilenode::int), 4, '0') "Table ID"
FROM pg_class c, pg_namespace n, pg_database d
WHERE c.relname = 'table name'
AND c.relnamespace = n.oid
AND d.datname=current_database();
In YugabyteDB, a table’s DocDB UUID corresponds to its relfilenode. The table ID can then be constructed from that UUID’s hexadecimal value.
Example:
yugabyte=# CREATE TABLE test(c1 INT PRIMARY KEY);
CREATE TABLE
yugabyte=# SELECT n.nspname "Schema Name",
yugabyte-# c.oid "YSQL OID",
yugabyte-# '0000' || lpad(to_hex(d.oid::int), 4, '0') || '00003000800000000000' || lpad(to_hex(c.relfilenode::int), 4, '0') "Table ID"
yugabyte-# FROM pg_class c, pg_namespace n, pg_database d
yugabyte-# WHERE c.relname = 'test'
yugabyte-# AND c.relnamespace = n.oid
yugabyte-# AND d.datname=current_database();
Schema Name | YSQL OID | Table ID
-------------+----------+----------------------------------
public | 16430 | 000034cb00003000800000000000402e
(1 row)
To make the SQL reusable, we can create a simple function to return the table_id for a given table!
CREATE OR REPLACE FUNCTION get_table_id(schema_name_p VARCHAR, table_name_p VARCHAR) RETURNS VARCHAR
AS $$
SELECT '0000' || lpad(to_hex(d.oid::int), 4, '0') || '00003000800000000000' || lpad(to_hex(c.relfilenode::int), 4, '0') tableid
FROM pg_class c, pg_namespace n, pg_database d
WHERE n.nspname = $1
AND c.relname = $2
AND c.relnamespace = n.oid
AND d.datname=current_database();
$$ LANGUAGE SQL;
Example:
yugabyte=# SELECT get_table_id('public', 'test');
get_table_id
----------------------------------
000034cb00003000800000000000402e
(1 row)
Have Fun!