1
2318
Question Meenakshi Shanmugam · Jul 16, 2020

Describe table in Cache db

#Caché #Databases #SQL

What is the sql command to describe table in cache db.

Discussion (3)1
Log in or sign up to continue

Comments

Cristiano Silva · Jul 17, 2020

Hi Meenakshi,

In the package %Dictionary you can query all information about classes.

To list all properties before compilation (columns):

SELECT Class.Name AS ClassName, Prop.Name AS PropertyName,  Prop.Type 
FROM %Dictionary.ClassDefinition AS ClassINNER JOIN %Dictionary.PropertyDefinition AS Prop ON Prop.parent = Class.%IDWHERE Class.Name = 'Sample.Person'
ClassName PropertyName Type
Sample.Person Age %Integer
Sample.Person DOB %Date
Sample.Person FavoriteColors %String
Sample.Person Home Address
Sample.Person Name %String
Sample.Person Office Address
Sample.Person SSN %String
Sample.Person Spouse Person

To list all properties after compilation (columns):

SELECT Class.Name AS ClassName, Prop.Name AS PropertyName, Prop.Type 
FROM %Dictionary.CompiledClass AS ClassINNER JOIN %Dictionary.CompiledProperty AS Prop ON Prop.parent = Class.%IDWHERE Class.Name = 'Sample.Person'
ClassName PropertyName Type
Sample.Person %%OID %Library.CacheString
Sample.Person %Concurrency %Library.CacheString
Sample.Person Age %Library.Integer
Sample.Person DOB %Library.Date
Sample.Person FavoriteColors %Library.String
Sample.Person Home Sample.Address
Sample.Person Name %Library.String
Sample.Person Office Sample.Address
Sample.Person SSN %Library.String
Sample.Person Spouse Sample.Person
0
Benjamin De Boe  Jul 17, 2020 to Cristiano Silva

the contents of those %Dictionary tables is a little geared towards class/object models. If you want a more SQL-focused view on your tables, you can look at the INFORMATION_SCHEMA package, which adheres to mainstream JDBC/ODBC dictionary structure and is used by mainstream SQL and BI tools like DBeaver, VSCode-SQLTools, Tableau, PowerBI, etc

Note you'll need to tick the "System" checkbox when browsing this schema in the System Management Portal.

0
Cristiano Silva  Jul 17, 2020 to Benjamin De Boe

Thanks Benjamin for the explanation,

Bellow the query in the INFORMATION_SCHEMA tables:

SELECT Tables.TABLE_SCHEMA, Tables.TABLE_NAME, Columns.COLUMN_NAME, Columns.DATA_TYPEFROM INFORMATION_SCHEMA.TABLES AS TablesINNER JOIN INFORMATION_SCHEMA.COLUMNS AS Columns 
   ON (Columns.TABLE_SCHEMA = Tables.TABLE_SCHEMA) AND (Columns.TABLE_NAME = Tables.TABLE_NAME)WHERE (Tables.TABLE_SCHEMA = 'Sample') AND (Tables.TABLE_NAME = 'Person')
TABLE_SCHEMA TABLE_NAME COLUMN_NAME DATA_TYPE
Sample Person ID integer
Sample Person Age integer
Sample Person DOB date
Sample Person FavoriteColors varchar
Sample Person Name varchar
Sample Person SSN varchar
Sample Person Spouse integer
Sample Person Home_City varchar
Sample Person Home_State varchar
Sample Person Home_Street varchar
Sample Person Home_Zip varchar
Sample Person Office_City varchar
Sample Person Office_State varchar
Sample Person Office_Street varchar
Sample Person Office_Zip varchar
0