SQL/MX 3.1 Installation and Management Guide (H06.23+, J06.12+)

Querying SQL/MX Metadata
HP NonStop SQL/MX Release 3.1 Installation and Management Guide—663852-001
8-61
Displaying All Tables Containing a Selected Column
Displaying All Tables Containing a Selected Column
This example displays all tables in schema SAMDBCAT.SALES that contain the
column ORDERNUM and displays the SQL data type of the column:
>> select substring(column_name, 1, 20) as column_name,
sql_data_type,
substring(object_name, 1, 20) as table_name
from cols c, objects o
where
c.object_uid = o.object_uid and
column_name = 'ORDERNUM' and
o.object_type = 'BT' 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
from nonstop_sqlmx_<sys
name>.system_schema.catsys
where cat_name = 'SAMDBCAT'
)
)
for read uncommitted access;
COLUMN_NAME SQL_DATA_TYPE TABLE_NAME
-------------------- ------------------ --------------------
ORDERNUM UNSIGNED NUMERIC ODETAIL
ORDERNUM UNSIGNED NUMERIC ORDERS