Displaying the Table Structure in MaxDB databases
Tags:
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.