SQL/MX 3.2 Installation and Upgrade Guide (H06.25+, J06.14+)

B Identifying tables that contain an IDENTITY Column
The following steps use queries to extract names of tables with an IDENTITY column. These queries
must be run against version 3100 or later metadata.
-- Execute all of the following from within mxci.
-- Set pattern $$SYSTEM$$ to the name of the system on which the queries are
-- executing.
set pattern $$SYSTEM$$ YOURSYS;
-- All of the following statements between the "--Begin Repeat" and
-- "--End Repeat" comments must be executed for every catalog on the system,
-- changing the following "set catalog" statement argument to match the next
-- catalog in sequence for each iteration.
-- Begin Repeat for every catalog on the system...
-- Set catalog to <catalog name>
set catalog YOURCAT;
Select
obj.object_name,schm.schema_name
from definition_schema_version_3100.objects obj,
definition_schema_version_3100.sg_usage sgu,
nonstop_sqlmx_$$SYSTEM$$.system_schema.schemata schm
where obj.object_uid = sgu.using_object_obj_uid and obj.schema_uid =
schm.schema.uid;
-- End Repeat for every catalog on the system...
58 Identifying tables that contain an IDENTITY Column