DESC Command in H2 Database

1. Overview

The H2 database is a lightweight, open-source relational database engine widely used by Java developers for testing, prototyping, and lightweight storage needs. While H2 supports many standard SQL commands and has a strong feature set, it doesn’t support the commonly used DESC (or DESCRIBE) command to inspect table structures.

In this tutorial, we’ll look at how to view table metadata in H2, explore some effective alternatives to DESC, and work through practical examples.

2. Understanding the DESC Command

The DESC command is frequently used in SQL to describe the structure of a table or a view, providing metadata about column names, data types, and constraints. It’s useful for developers and database administrators who need a quick overview of a table’s schema without referring to the original SQL scripts that created it.

However, H2 doesn’t explicitly support the DESC command. Instead, we can use commands like SHOW or query built-in INFORMATION_SCHEMA tables to retrieve similar metadata.

3. Setting up an Example

To demonstrate the alternatives to DESC in H2, let’s start by creating a sample table that we’ll use in our examples:

CREATE TABLE EMPLOYEE (
    ID INT PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL,
    AGE INT COMMENT 'The age of an employee",
    SALARY DECIMAL(15, 2) DEFAULT 1000.0
);

This table includes a mix of data types, a primary key, nullability and default constraints, and a comment on one of the columns.

Since H2 doesn’t natively support the DESC command, attempting to execute it results error:

Syntax error in SQL statement "DESC EMPLOYEE[*]"; expected "ALTER, ANALYZE, COMMENT,
  COMMIT, CREATE, DELETE, DROP, EXPLAIN, GRANT, INSERT, MERGE, PREPARE, REVOKE, ROLLBACK,
  SELECT, SET, SHOW, TRUNCATE, UPDATE, UPSERT"; SQL statement:
DESC EMPLOYEE [42001-214]

By contrast, the same command works seamlessly in databases like Oracle:

+--------+------------------+----------+-----+---------+------------------------+
| COLUMN | DATA TYPE        | NULLABLE | KEY | DEFAULT | REMARK                 |
+--------+------------------+----------+-----+---------+------------------------+
| ID     | NUMBER           | NO       | PRI | NULL    | NULL                   |
| NAME   | VARCHAR2(50)     | NO       |     | NULL    | NULL                   |
| AGE    | NUMBER           | YES      |     | NULL    | The age of an employee |
| SALARY | NUMBER(15, 2)    | YES      |     | 1000.0  | NULL                   |
+--------+------------------+----------+-----+---------+------------------------+

Therefore, we must rely on alternative methods to retrieve table metadata. These approaches will be explored in the following sections.

4. Using the SHOW Command

The SHOW command in H2 is a straightforward alternative to DESC, offering a quick way to retrieve metadata. With SHOW, we can list available schemas, tables, views, and columns for a specified table.

4.1. Printing Schemas

Let’s display all available schemas in our database:

SHOW SCHEMAS;

This returns the schemas currently available in our H2 instance:

+------------------+
|SCHEMA_NAME       |
+------------------+
|INFORMATION_SCHEMA|
|PUBLIC            |
+------------------+

By default, H2 has two main schemas:

• PUBLIC: Contains all user-created tables and objects unless specified otherwise
• INFORMATION_SCHEMA: Holds metadata about tables, views, and other database objects

The PUBLIC schema is where we’ll find our EMPLOYEE table, as it’s created there by default unless specified otherwise.

4.2. Printing Tables

Let’s list all tables in the database:

SHOW TABLES;

This shows available tables and the schema they belong to:

+----------+------------+
|TABLE_NAME|TABLE_SCHEMA|
+----------+------------+
|EMPLOYEE  |PUBLIC      |
+----------+------------+

If we want to list tables within a specific schema, we can specify the schema name:

SHOW TABLES FROM INFORMATION_SCHEMA;

This command provides a list of all the views and metadata tables available in a given schema:

+-----------------+------------------+
|TABLE_NAME       |TABLE_SCHEMA      |
+-----------------+------------------+
|CHECK_CONSTRAINTS|INFORMATION_SCHEMA|
|COLLATIONS       |INFORMATION_SCHEMA|
|COLUMNS          |INFORMATION_SCHEMA|
...

4.3. Printing Columns

To view the structure of a specific table, we can use the SHOW COLUMNS command, which lists all columns in a table along with their data types and constraints.

Let’s look at the columns in our EMPLOYEE table:

SHOW COLUMNS FROM EMPLOYEE;

This returns information about the table we created earlier:

+------+---------------------+----+---+-------+
|FIELD |TYPE                 |NULL|KEY|DEFAULT|
+------+---------------------+----+---+-------+
|ID    |INTEGER              |NO  |PRI|NULL   |
|NAME  |CHARACTER VARYING(50)|NO  |   |NULL   |
|AGE   |INTEGER              |YES |   |NULL   |
|SALARY|DECIMAL(15, 2)       |YES |   |1000.0 |
+------+---------------------+----+---+-------+

While the command is straightforward and provides essential details on column names, types, and constraints, it doesn’t include comments or other extended metadata. For more comprehensive information, we need to query the INFORMATION_SCHEMA directly, which we’ll do next.

5. Using INFORMATION_SCHEMA for Detailed Metadata

If we need more flexibility and detail than the SHOW command, the INFORMATION_SCHEMA offers a powerful alternative. We can retrieve comprehensive information about schemas, tables, columns, and other database objects by querying metadata tables.

5.1. Printing Schemas

To view all schemas in the H2 database, we can query the SCHEMATA table:

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

Again, this returns the schemas in our H2 instance:

+------------------+
|SCHEMA_NAME       |
+------------------+
|INFORMATION_SCHEMA|
|PUBLIC            |
+------------------+

In addition to the schema name, the SCHEMATA table provides more information about each schema, such as the schema owner and encoding, which are details unavailable through the SHOW command.

5.2. Printing Tables

To retrieve all tables in the database using INFORMATION_SCHEMA, we can query the TABLES table:

SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES;

The query prints:

+----------+------------+
|TABLE_NAME|TABLE_SCHEMA|
+----------+------------+
|EMPLOYEE  |PUBLIC      |
+----------+------------+

If we only want tables in a specific schema, we can add a WHERE clause:

SELECT TABLE_NAME, TABLE_SCHEMA 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA';

This provides a list of all metadata tables in the specified schema:

+-----------------+------------------+
|TABLE_NAME       |TABLE_SCHEMA      |
+-----------------+------------------+
|CHECK_CONSTRAINTS|INFORMATION_SCHEMA|
|COLLATIONS       |INFORMATION_SCHEMA|
|COLUMNS          |INFORMATION_SCHEMA|
...

5.3. Printing Columns

To retrieve detailed column information, we can query the COLUMNS table. This provides metadata for each column, including data types and constraints.

Let’s retrieve column details for our EMPLOYEE table again:

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, REMARKS
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'EMPLOYEE';

Here’s the output:

+----------------+-----------+-----------------+-----------+--------------+-----------------------+
|ORDINAL_POSITION|COLUMN_NAME|DATA_TYPE        |IS_NULLABLE|COLUMN_DEFAULT|REMARKS                |
+----------------+-----------+-----------------+-----------+--------------+-----------------------+
|1               |ID         |INTEGER          |NO         |null          |null                   |
|2               |NAME       |CHARACTER VARYING|NO         |null          |null                   |
|3               |AGE        |INTEGER          |YES        |null          |The age of the employee|
|4               |SALARY     |NUMERIC          |YES        |1000.0        |null                   |
+----------------+-----------+-----------------+-----------+--------------+-----------------------+

Unlike the SHOW COLUMNS command, querying the COLUMNS table provides additional information and customization options, such as the ability to filter by specific columns or attributes. While the SHOW command does not display comments or certain constraints, we can retrieve them from the INFORMATION_SCHEMA tables if necessary.

6. Conclusion

Despite lacking a direct DESC command, the H2 Database offers flexible ways to retrieve detailed metadata through the SHOW command and INFORMATION_SCHEMA tables.

The SHOW command provides quick and straightforward access to schemas, tables, and columns, while INFORMATION_SCHEMA offers more comprehensive and customizable metadata queries, covering additional information such as data types, default values, keys, and even comments on columns.

By using both methods effectively, we can easily inspect and manage H2 database metadata, ensuring a smooth development experience.