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.oid::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();
Example:
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.oid::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 | 16384 | 000033e8000030008000000000004000
(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.oid::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=# CREATE OR REPLACE FUNCTION get_table_id(schema_name_p VARCHAR, table_name_p VARCHAR) RETURNS VARCHAR
yugabyte-# AS $$
yugabyte$# SELECT '0000' || lpad(to_hex(d.oid::int), 4, '0') || '00003000800000000000' || lpad(to_hex(c.oid::int), 4, '0') tableid
yugabyte$# FROM pg_class c, pg_namespace n, pg_database d
yugabyte$# WHERE n.nspname = $1
yugabyte$# AND c.relname = $2
yugabyte$# AND c.relnamespace = n.oid
yugabyte$# AND d.datname=current_database();
yugabyte$# $$ LANGUAGE SQL;
CREATE FUNCTION
yugabyte=# SELECT get_table_id('public', 'test');
get_table_id
----------------------------------
000033e8000030008000000000004000
(1 row)
Have Fun!