Displaying the Table Structure in MaxDB databases

If you work in database area, sometimes it's useful to know a given table structure(what are the fields and their types, etc..).

In Oracle databases, this work can be done quite easily using the DESC command:

SQL> desc "SAPGFP"."T000"

Name Null?    Type

----------------------------------------- -------- ----------------------------

MANDT                                     NOT NULL VARCHAR2(9)

MTEXT                                     NOT NULL VARCHAR2(75)

ORT01                                     NOT NULL VARCHAR2(75)

MWAER                                     NOT NULL VARCHAR2(15)

ADRNR                                     NOT NULL VARCHAR2(30)

CCCATEGORY                                NOT NULL VARCHAR2(3)

CCCORACTIV                                NOT NULL VARCHAR2(3)

CCNOCLIIND                                NOT NULL VARCHAR2(3)

CCCOPYLOCK                                NOT NULL VARCHAR2(3)

CCNOCASCAD                                NOT NULL VARCHAR2(3)

CCSOFTLOCK                                NOT NULL VARCHAR2(3)

CCORIGCONT                                NOT NULL VARCHAR2(3)

CCIMAILDIS                                NOT NULL VARCHAR2(3)

CCTEMPLOCK                                NOT NULL VARCHAR2(3)

CHANGEUSER                                NOT NULL VARCHAR2(36)

CHANGEDATE                                NOT NULL VARCHAR2(24)

LOGSYS                                    NOT NULL VARCHAR2(30)

However when it comes to MaxDB databases, this is a little bit harder to accomplish, as we don't have any similar command, but we can pretty much obtain the same result by executing a manual query over the MaxDB System table DOMAIN.COLUMNS.

So, the above example in MaxDB would be:

SELECT

  columnname, datatype, len, dec, codetype, mode

FROM

  DOMAIN.COLUMNS

WHERE

  tablename = 'T000'

And our result is:

COLUMNNAME         DATATYPE      LEN;    DEC    CODETYPE   MODE

"MANDT"                      "VARCHAR"     15      ?      "UNICODE"  "KEY"

"MTEXT"                      "VARCHAR"     0535    ?      "UNICODE"  "OPT"

"ORT01"                      "VARCHAR"     0535    ?      "UNICODE"  "OPT"

"MWAER"                    "VARCHAR"     35      ?      "UNICODE"  "OPT"

"ADRNR"                      "VARCHAR"     9484    ?      "UNICODE"  "OPT"

"CCCATEGORY"           "VARCHAR"     94      ?      "UNICODE"  "OPT"

"CCCORACTIV"            "VARCHAR"     94      ?      "UNICODE"  "OPT"

"CCNOCLIIND"              "VARCHAR"     94      ?      "UNICODE"  "OPT"

"CCCOPYLOCK"           "VARCHAR"     94      ?      "UNICODE"  "OPT"

"CCNOCASCAD"           "VARCHAR"     94      ?      "UNICODE"  "OPT"

"CCSOFTLOCK"            "VARCHAR"     94      ?      "UNICODE"  "OPT"

"CCORIGCONT"             "VARCHAR"     94      ?      "UNICODE"  "OPT"

"CCIMAILDIS"                "VARCHAR"     94      ?      "UNICODE"  "OPT"

"CCTEMPLOCK"            "VARCHAR"     94      ?      "UNICODE"  "OPT"

"CHANGEUSER"           "VARCHAR"     9405    ?      "UNICODE"  "OPT"

"CHANGEDATE"           "VARCHAR"     65      ?      "UNICODE"  "OPT"

"LOGSYS"                     "VARCHAR"     9484    ?      "UNICODE"  "OPT"

Interpreting this result we see that it's quite similar to the Oracle output, except by the fields CODETYPE, which informs whether the field is an unicode, ASCII char and MODE, which tells us whether the field is part of the PRIMARY KEY("KEY"), whether it's value can be null("OPT") or if the field must not be null("MAN").

Another good thing to know, is how the primary key of a table is defined. In MaxDB we can use the following statement:

SELECT

   columnname

FROM

   DOMAIN.COLUMNS

WHERE

   tablename = 'DD03L'

AND

   mode = 'KEY'

ORDER BY

   keypos

This statement selects all fields that are part of the PRIMARY KEY(mode = 'KEY') from Table DD03L and orders the result by the position of the field in the KEY definition. The result is:

       COLUMNNAME

1     "TABNAME"

2     "FIELDNAME"

3     "AS4LOCAL"

4     "AS4VERS"

5     "POSITION"

These are some examples of what we can do using the System Table DOMAIN.COLUMS. The following MaxDB help entry shows a description of each column of this table, which helps to assembly our own queries over MaxDB tables:

http://maxdb.sap.com/doc/7_7/45/093a84010a4574e10000000a114a6b/content.htm

The above queries can help in situations on which we need to know how exactly a table is defined in the MaxDB database.