SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Querying SQL/MX Metadata
HP NonStop SQL/MX Installation and Management Guide—523723-004
8-57
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_figaro.system_schema.schemata
where
schema_name = 'SALES' and
cat_uid =
(select cat_uid
from nonstop_sqlmx_figaro.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