SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

All queries for information about columns must access information from the CATSYS, SCHEMATA,
and OBJECTS tables, and from other appropriate tables, such as the COLS table.
You can also use the MXCI SHOWDDL command to display the approximate CREATE TABLE
statement that might have created the columns in that table. For examples of the SHOWDDL
command, see the SQL/MX Reference Manual.
Displaying all Columns in a Table
This example displays all columns in the table SAMDBCAT.PERSNL.EMPLOYEE:
>> set schema samdbcat.definition_schema_version_<schema version>;
--- SQL operation complete.
>> select substring(column_name, 1, 25) as column_name,
column_number
from cols c, objects o
where
c.object_uid = o.object_uid and
o.object_name = 'EMPLOYEE' and
o.object_type = 'BT' and
o.schema_uid =
(select schema_uid
from nonstop_sqlmx_sys name>.system_schema.schemata
where
schema_name = 'PERSNL' and
cat_uid =
(select cat_uid
from nonstop_sqlmx_<sys name>.system_schema.catsys
where cat_name = 'SAMDBCAT'
)
)
order by column_number
for read uncommitted access;
COLUMN_NUMBER
-------------
COLUMN_NAME
-------------------------
0
1
EMPNUM
FIRST_NAME
2LAST_NAME
3DEPTNUM
4
5
JOBCODE
SALARY
Displaying all Columns in a View
This example displays all columns in the view SAMDBCAT.SALES.CUSTLIST:
>> set schema samdbcat.definition_schema_version_<schema version>;
--- SQL operation complete.
>> select substring(column_name, 1, 25) as column_name,
column_number
from cols c, objects o
where
c.object_uid = o.object_uid and
o.object_name = 'CUSTLIST' and
o.object_type = 'VI' and
o.schema_uid =
(select schema_uid
from nonstop_sqlmx_<sys name>.system_schema.schemata
where
schema_name = 'SALES' and
cat_uid =
(select cat_uid
Displaying Column Information 139