Reverse engineering a HANA table to create a .hdbtable file

[Former Member]

We all start out doing things the wrong way, and then wonder how we get ourselves back out of it.

Over the last couple of weeks, I've been trying to bring ourselves into a better development method by building objects into HANA using the SAP HANA Development perspective. Unfortunately, we've been and built a whole series of tables using SQL. I know, bad boys and so on, but we're trying to get back on the right track.

So we've finally started building our new tables using hdbtable files (and hdbdd files), but how can we reverse engineer the tables we've already built?

The answer lied in the catalog tables.

What started as a seemingly painful problem, ended up as a fairly simple query that I wrote and fine tuned in a couple of hours in between other things

This is what I ended up:

SELECT

  '{name = "' || A.COLUMN_NAME || '";'

  || ' sqlType = ' || A.DATA_TYPE_NAME

  || '; nullable = ' || LOWER(A.IS_NULLABLE)

  || ';' ||

  CASE

    WHEN A.DATA_TYPE_NAME IN ('CHAR','VARCHAR')

      THEN ' length = ' || TO_CHAR(A.LENGTH) || ';'

    WHEN A.DATA_TYPE_NAME = 'FLOAT'

      THEN ' precision = ' || TO_CHAR(A.LENGTH) || '; scale = ' || TO_CHAR(A.SCALE) || ';'

    WHEN A.DATA_TYPE_NAME = 'DECIMAL'

      THEN ' precision = ' || TO_CHAR(A.LENGTH) || '; scale = ' || TO_CHAR(A.SCALE) || ';'

    ELSE ''

  END

  ||

  CASE

    WHEN A.COMMENTS IS NULL THEN ''

    ELSE ' comment = "' || A.COMMENTS || '";'

  END

  || '},' AS COLUMN_COMMAND

FROM TABLE_COLUMNS A

WHERE A.SCHEMA_NAME = <schema_name>

AND A.TABLE_NAME = <table_name>

ORDER BY A.COLUMN_ID

The script output looks like this excerpt from one of our tables:

{name = "CCYY_IND"; sqlType = VARCHAR; length = 4;},

{name = "ACCT_PERIOD"; sqlType = INTEGER;},

{name = "ACTUAL_AMOUNT"; sqlType = DECIMAL; precision = 15; scale = 2;},

Once I have this, it's just a matter of creating a new hdbtable file, completing the schema name using the demo lines and pasting in the results from the above script. I've attached a copy of the script file, and an actual file from our landscape.

Hope this helps someone else out there.