Fully managed cloud. 30-day FREE full featured trial. Start Now
Showing results for 
Search instead for 
Did you mean: 
Community Manager
Community Manager


EXAplus and many other database browsers provide the functionality to create the DDL for the database.
This solution is a Lua script to do the same task and generates the DDL for each database object based on the system tables.


There are parameters to influence the output style. Documentation on the parameters and some known restrictions can be found at the beginning of the script. The latest release covers all supported database versions. Please see the header of the script for more information on what is supported and the current limitations.


Parameter Data Type Meaning
add_user_structure Boolean If true then DDL for adding roles and users is added (at the top, before everything else).
add_rights Boolean If true then DDL for user & role privileges is added (at the bottom, after everything else).
store_in_table Boolean If true, the entire output is stored in the table "DB_HISTORY"."DATABASE_DDL" before the output is displayed.



You can execute the script with the below statement:

-- parameters: add_user_structure, add_rights, store_in_table
execute script create_db_DDL(true, true, false);


Additional References


This version has a bug on line 661 in the function add_script_to_DDL where it tries to retrieve the source of a script

function add_script_to_DDL(schema_namescript_name)                --ADD SCRIPT-------------------------------------------------------------------------------------------
    as1_success, as1_res = pquery([[SELECT SCRIPT_SCHEMA, SCRIPT_TEXT FROM EXA_DBA_SCRIPTS WHERE SCRIPT_NAME=:s]], {s=script_name})
        if not as1_success then
            error('Error at as1')
    sqlstr_add('-- BEGIN OF SCRIPT: '..schema_name..'.'..script_name..' ======================================================================================================\n')
    sqlstr_add('\nOPEN SCHEMA \"'..schema_name..'\";')    --Open schema to create the script
    sqlstr_add('\nCLOSE SCHEMA;')
    sqlstr_add('\n-- END OF SCRIPT: '..schema_name..'.'..script_name..' ======================================================================================================\n')

The sql query doesn't filter on SCRIPT_SCHEMA. It also ignores the SCRIPT_SCHEMA returned by the sql query. Together this means it can backup the wrong source for scripts in different schemas with the same name.


Hi @exa-Nico , the labels do state that this is only applicable to V.6.0 & V.6.1 and yes, when running this on a V.7 I do have to adjust the GRANT PRIORITY GROUP ( now ALTER USER ... SET CONSUMER_GROUP ) and the corresponding fields from exa_dba_users - is there a particular reason why this is not hosted on github so that the community can pull- & merge something like this ? Or is it and I just wasn´t able to look it up ?


Community Manager
Community Manager

@mwellbro we are working to move all of the scripts to Github for exactly that reason 😁



any news on moving this to github?


alter @exa-Nico set ping_keep_alive=30 days;  -- 😉

Community Manager
Community Manager

@mwellbro I've nudged some of my colleagues who are working on it 😅

Team Exasol
Team Exasol

Hi @mwellbro , hi @Charlie , hi @Joep ,

As you might have noticed, that script was updated and moved to github some time ago.

Today we've updated it there to make it work even better with version 7.

In particular, concerns regarding add_script_to_DDL and priority/consumer groups were addressed.

Feel free to test out and let us know if you have further questions!


Hello. While executing the provided CREATE OR REPLACE LUA script I get the following error:


Errore SQL [42000]: failed to create script: syntax error in line 1137: <eof> expected near 'OR' (Session: 1724268256439566336)



databaseProductName = EXASolution
databaseProductVersion = 7.1.4


Yep, me too @ecoh_dr. It seems that there are three scripts in this file. You just need to select the first one on its own and run it.