Describe table in Cache db

Question

Question

Meenakshi Shanmugam · Jul 16, 2020

#Databases #SQL #Caché

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

Discussion (3)1

Log in or sign up to continue

Cristiano Silva · Jul 17, 2020

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_TYPE
FROM INFORMATION_SCHEMA.TABLES AS Tables
INNER 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 1

score 1 · Answer 1 · 2020-07-17T06:53:29-04:00

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 Class
INNER JOIN %Dictionary.PropertyDefinition AS Prop ON Prop.parent = Class.%ID
WHERE 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 Class
INNER JOIN %Dictionary.CompiledProperty AS Prop ON Prop.parent = Class.%ID
WHERE 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

score 1 · Answer 2 · 2020-07-17T07:30:37-04:00

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.