SQL to display all datadictionary tables and their corresponding columns with attributes

Products

• Datacom
• DATACOM - AD
• Datacom/AD
• Datacom/DB

Issue/Introduction

How can I run a batch DBSQLPR to query datadictionary using SQL to print off all of the tables and their corresponding columns with attributes?

Environment

Release: 15.1

Resolution

To display all tables in datadictionary use this SQL: 

SELECT DATABASE_ID, AUTHID, ENTITY_NAME, SQLNAME, SQL_INTENT
FROM SYSADM.AGGREGATE
WHERE
 ENTITY_TYPE = 'TBL' AND
 STATUS = 'P' 
ORDER BY DATABASE_ID;

To display all fields/columns and attributes in a table use this SQL:

SELECT AUTHID, RECORD_NAME, SEQNO, ENTITY_NAME,  
 TYPE, LENGTH, PRECISION, SIGN,   
 SEMANTIC_TYPE, NULL_INDICATOR     
FROM SYSADM.FIELD 
WHERE ELM_NAME = ' ' AND
 STATUS = 'P' AND
 RECORD_NAME = 'table_name' 
ORDER BY SEQNO;                                                  

Replace 'table_name'  with the name of the table to report on.

Additional Information

For reporting on SQL tables only see article Datacom SQL metadata for database tables and columns.