sqlh2ddl

H2 ddl create table

I would like to know if it is possible to get the H2 ddl create table from the 

http://localhost:5678/pecador/console/

I have tried show columns from T_USER ;

but it only shows the columns

Solution

  • If you want to get SQL for a table T_USER, you can use one of these commands in H2:

    -- All versions
SCRIPT NODATA NOPASSWORDS NOSETTINGS TABLE T_NAME;

-- H2 1.4.200 and older versions only:
SELECT SQL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T_NAME';

-- H2 2.0.202 and newer versions only:
SELECT DB_OBJECT_SQL('TABLE', 'PUBLIC', 'T_NAME');

    The first one also returns some unrelated elements. Actually you can dump the whole database with it, check its documentation for details: https://h2database.com/html/commands.html#script

    The second one can be used in historic versions of H2. It returns only the SQL of the specified table (unless you have tables with the same name in multiple schemas, but you can add AND SCHEMA_NAME = 'PUBLIC' to exclude them). Note that you need to specify 'T_NAME' in its real case, if you're using ;DATABASE_TO_LOWER=TRUE setting it is 't_name'.

    Third one for modern versions of H2 also returns definition of specified table. Schema and table names must be specified in the correct case, just like with the previous query.