Querying metadata

You can query SAP ASE system tables to retrieve metadata about objects. These tables can be queried directly to return information about tables, columns, constraints, triggers, indexes, and more.

Sample queries

List tables and their columns in alphabetical order

Returns all user tables and their associated columns in the database, ordered alphabetically.

SELECT 
    o.name AS table_name,
    c.name AS column_name
FROM 
    sysobjects o
JOIN 
    syscolumns c ON o.id = c.id
WHERE 
    o.type = 'U'
ORDER BY 
    o.name ASC,
    c.name ASC;

List of table columns with their type, length, and nullable attribute

Shows detailed information about the columns of the user table such as their datatype, length, nullable attribute, and the primary-key columns in the alphabetical column order.

SELECT 
    o.name AS table_name,
    c.name AS column_name,
    t.name AS data_type,
    c.length AS column_length,
    CASE WHEN c.status & 8 = 8 THEN 'NOT NULL' ELSE 'NULL' END AS nullability,
    CASE WHEN cons.status = 1 THEN 'PRIMARY KEY' ELSE '' END AS key_type
FROM 
    sysobjects o
JOIN 
    syscolumns c ON o.id = c.id
JOIN 
    systypes t ON c.usertype = t.usertype
LEFT JOIN sysconstraints cons 
    ON cons.tableid = o.id AND cons.colid = c.colid AND cons.status = 1
WHERE 
    o.type = 'U'
    AND o.name = 'my_table'
ORDER BY 
    c.name ASC;