Skip to main content

db_object_id

1. DB_OBJECT_ID Function​

The DB_OBJECT_ID function in H2 database is used to retrieve the unique identifier for a database object.

2. Syntax​

The syntax for the DB_OBJECT_ID function in H2 database is as follows:

DB_OBJECT_ID(object_type, object_name)

Arguments​

  • object_type: The type of the database object. It should be a string representing the object type, such as 'TABLE', 'VIEW', 'SEQUENCE', 'INDEX', 'CONSTRAINT', etc.
  • object_name: The name of the database object. It should be a string representing the name of the object.

Return​

  • The DB_OBJECT_ID function returns a unique identifier for the specified database object. The identifier is an integer value.

3. Notes​

  • The DB_OBJECT_ID function in H2 database can be used to obtain the identifier of various database objects, such as tables, views, sequences, indexes, and constraints.
  • If the specified object does not exist or if the object name is ambiguous (i.e., multiple objects with the same name exist in different schemas), the DB_OBJECT_ID function will return NULL.
  • The object type is case-insensitive, but the object name is case-sensitive. Ensure that you provide the correct case for the object name while using the DB_OBJECT_ID function.
  • The object type and object name can be dynamically provided as strings or as column values from a table.

4. Examples​

Here are a few examples demonstrating the usage of the DB_OBJECT_ID function in H2 database:

Example 1 - Retrieving the ID of a table:​

SELECT DB_OBJECT_ID('TABLE', 'employees') AS table_id;

Output:

table_id
--------
123456

Example 2 - Retrieving the IDs of multiple tables using a column value:​

CREATE TABLE table_names (
  id INT PRIMARY KEY,
  table_name VARCHAR(50)
);

INSERT INTO table_names VALUES (1, 'employees'), (2, 'departments'), (3, 'orders');

SELECT id, table_name, DB_OBJECT_ID('TABLE', table_name) AS table_id
FROM table_names;

Output:

id | table_name  |  table_id
---+-------------+-----------
1  | employees   |  123456
2  | departments |  789012
3  | orders      |  345678
  • DB_OBJECT_NAME - Retrieve the name of a database object based on its identifier.
  • DB_OBJECT_DEFINITION - Retrieve the SQL definition of a database object.