HANA Database Object Definition and DDL Create SQLScript by Code

For SAP HANA database SQL developers it is important to get the CREATE TABLE or CREAT DDL SQLScript of that HANA database object by code. Fetching the CREATE SQL statement also helps SQL programmers to display the source code of the HANA database objects like CDS views, AMDP procedures (ABAP Managed Database Procedure), and, of course last but not least database tables. Fortunately, SAP HANA database has a built in procedure named GET_OBJECT_DEFINITION to use for reading the SQL metadata of the requested database objects. In this HANA database tutorial for SQLScript developers, I want to show how programmers can execute the native procedure with samples.

In addition to the method of getting the CREATE statement of database table objects shared in this HANA database tutorial, SQL developers can get similar results on SAP HANA Studio via GUI as described in following tutorials; SAP HANA Studio Export Wizard to Generate Create SQL Statements for Database Tables and Generate Create Table DDL SQL Script of SAP HANA Database Table

Create Table SQLScript Statement on SAP HANA Database

For example, if the database developer wants to get the CREATE Table DDL statement for a table object in a specific schema, then the syntax in following SQL command can be used.


call get_object_definition('SAPABAP1', 'T002')

Code

The first parameter to the procedure GET_OBJECT_DEFINITION is the schema name and the second parameter is the object name that the SQL developer is interested in.
SAPABAP1 is the default schema where the SAP objects are created within.
T002 is the Language Keys database table name. Following screenshot shows the table definition on SE11 SAP transaction screen.

SAP table definition on SE11 transaction screen

And following is the output of the procedure get_object_definition for our sample SAP table.

get_object_definition procedure output for SAP HANA database table

If you right click on Object_Creation_Statement column and select Copy Cells menu option, the CREATE TABLE SQL command will be copied to clipboard

copy CREATE TABLE SQL statement for SAP HANA database table

Now paste the copied cell values on a new SQL Console on SAP HANA Studio
Then exclude the column name OBJECT_CREATION_STATEMENT which is the first line and highlight all other SQLScript code.
Right click to show the context menu option Format to format SQL code

format SQLScript code on SAP HANA Studio SQL console

Here is the formatted SQL code with SQLScript CREATE TABLE statement for sample SAP HANA database table T002

create table SQL command for sample SAP HANA database table

I copied below the SQLScript DDL Create Table statement for T002 table


CREATE COLUMN TABLE "SAPABAP1"."T002" ("SPRAS" NVARCHAR(1) DEFAULT '' NOT NULL ,

 "LASPEZ" NVARCHAR(1) DEFAULT '' NOT NULL ,

 "LAHQ" NVARCHAR(1) DEFAULT '' NOT NULL ,

 "LAISO" NVARCHAR(2) DEFAULT '' NOT NULL ,

 CONSTRAINT "T002~0" PRIMARY KEY ("SPRAS")) UNLOAD PRIORITY 5 AUTO MERGE

Code

Create View SQL DDL Statement for SAP HANA Database CDS View

For CDS views, the same syntax can be used to get the CREATE VIEW SQL DDL statement and display the source code of a database view on SAP HANA Studio easily using SQL as follows


call get_object_definition ('SAPABAP1', '/1BS/SADL_CDS')

Code

Here is the SQL output when I call the system procedure GET_OBJECT_DEFINITON for the sample CDS view database object

SQLScript create view SQL DDL statement for a CDS view on SAP HANA database

Create Procedure SQL Statement for SAP HANA AMDP Procedures

On SAP HANA systems, ABAP programmers can create AMDP procedures (ABAP Managed Database Procedure) and procedure methods that can be called from ABAP code.
If you develop AMDP procedures, programmers can require to access the source codes of the AMDP methods by SQL code, too.

Here is a get_object_definition procedure CALL to get details about a sample AMDP procedure


call get_object_definition ('SAPABAP1', '/KODYAZ/SOM_INFO_ORDER_AMDP=>P_SEL_VBAKUK_VBAP')

Code

Now the object_creation_statement can be copied as shown before in this tutorial.

AMDP create procedure statement

When I paste the copied code, I can see the source code of the CREATE PROCEDURE command as follows

SAP HANA AMDP Create Procedure SQL code

Create Statement for Builtin Database Objects

I wondered the CREATE PROCEDURE source code for the get_object_definition system procedure and execute the procedure for itself as seen in below SQL command.
Please note that instead of SAPABAP1 schema name this time I used SYS as the first input parameter value to the get_object_definition procedure call.


call get_object_definition ('SYS', 'GET_OBJECT_DEFINITION')

Code

Unfortunately, I as understand for build-in database objects the GET_OBJECT_DEFINITION procedure does not provide detailed information.


CREATE PROCEDURE GET_OBJECT_DEFINITION (IN schema NVARCHAR(256), IN object NVARCHAR(256) ) READS SQL DATA AS BUILTIN

Code

I hope SAP HANA database programmers will find this SQLScript tutorial useful to get the CREATE DDL statements of SQL database objects.