Is there an SQL query that gets the application database metadata?
We are using Datacom Server to get the metadata that will be imported into another database.
Release : 15.1
Component : CA DATACOM SQL
Datacom does not store the CREATE statements metadata for SQL.
However the information can be obtained from other system databases.
A list of SQL tables can be obtained by querying the directory dynamic system table DIR_TABLE.
SELECT SQL_AUTHID, TABLE_SQLNAME, SQL_INTENT FROM SYSADM.DIR_TABLE
WHERE SQL_INTENT = 'Y' OR SQL_INTENT = 'R';
There is also a DIR_COLUMN dynamic system table but it does not have SQL information.
Article 221686 shows a method of getting the SQL column names. For example to get the columns for the ORDERS table:
SELECT * FROM SYSADM.ORDERS FETCH FIRST 1 ROWS ONLY;
Alternatively you can query the dataditionary FIELD table to get SQL column information:
SELECT AUTHID, AGR_SQLNAME, SEQNO, SQLNAME,
TYPE, LENGTH, PRECISION, SIGN,
WHERE ELM_NAME = ' ' AND STATUS = 'P' AND AGR_SQLNAME = 'ORDERS'
ORDER BY SEQNO;