Display a Table’s TABLE_ID in YSQL

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!