System Tables

Home

4D v20

System Tables

The SQL catalogue of 4D includes several system tables, which can be accessed by any SQL user having read access rights: _USER_TABLES, _USER_COLUMNS, _USER_INDEXES, _USER_CONSTRAINTS, _USER_IND_COLUMNS, _USER _CONS_ COLUMNS, _USER_SCHEMAS, _USER_VIEWS and _USER_VIEW_COLUMNS.

In accordance with the customs of SQL, system tables describe the database structure. Here is a description of these tables and their fields:

*_USER_TABLES*		Describes the user tables of the database
TABLE_NAME	VARCHAR	Table name
TEMPORARY	BOOLEAN	True if the table is temporary; otherwise, false
TABLE_ID	INT64	Table number
SCHEMA_ID	INT32	Number of schema
REST_AVAILABLE	BOOLEAN	True if column is exposed with REST service; otherwise, False
LOGGED	BOOLEAN	True if table operations are included in log file; otherwise, False

*_USER_COLUMNS*		Describes the columns of the user tables of the database
TABLE_NAME	VARCHAR	Table name
COLUMN_NAME	VARCHAR	Column name
DATA_TYPE	INT32	Column type (See Data Types)
DATA_LENGTH	INT32	Column length
OLD_DATA_TYPE	INT32	4D field type (see the Type command)
NULLABLE	BOOLEAN	True if column accepts NULL values; otherwise, false
TABLE_ID	INT64	Table number
COLUMN_ID	INT64	Column number
UNIQUENESS	BOOLEAN	True if column is declared Unique; otherwise, False
AUTOGENERATE	BOOLEAN	True if column value is generated automatically for each new record; otherwise, False
AUTOINCREMENT	BOOLEAN	True if column value is incremented automatically; otherwise, False
REST_AVAILABLE	BOOLEAN	True if column is exposed with REST service; otherwise, False

*_USER_INDEXES*		Describes the user indexes of the database
INDEX_ID	VARCHAR	Index number
INDEX_NAME	VARCHAR	Index name
INDEX_TYPE	INT32	Index type (1=BTree / Composite, 3=Cluster / Keyword, 7=Auto, 8=Auto for Object type field)
KEYWORD	BOOLEAN	True if index is a keyword index; otherwise, False
TABLE_NAME	VARCHAR	Name of table with index
UNIQUENESS	BOOLEAN	True if index imposes a uniqueness constraint; otherwise, false
TABLE_ID	INT64	Number of table with index

*_USER_IND_COLUMNS*		Describes the columns of user indexes of the database
INDEX_ID	VARCHAR	Index number
INDEX_NAME	VARCHAR	Index name
TABLE_NAME	VARCHAR	Name of table with index
COLUMN_NAME	VARCHAR	Name of column with index
COLUMN_POSITION	INT32	Position of column in index
TABLE_ID	INT64	Number of table with index
COLUMN_ID	INT64	Column number

*_USER_CONSTRAINTS*		Describes the integrity constraints of the database
CONSTRAINT_ID	VARCHAR	Constraint number
CONSTRAINT_NAME	VARCHAR	Name associated with constraint definition
CONSTRAINT_TYPE	VARCHAR	Type of constraint definition (P=primary key, R=referential integrity - foreign key, 4DR=4D relation)
TABLE_NAME	VARCHAR	Name of table with constraint definition
TABLE_ID	INT64	Number of table with constraint
DELETE_RULE	VARCHAR	Delete rule for a referential constraint – CASCADE or RESTRICT
RELATED_TABLE_NAME	VARCHAR	Name of related table
RELATED_TABLE_ID	INT64	Number of related table

*_USER_CONS_COLUMNS*		Describes the columns of user constraints of the database
CONSTRAINT_ID	VARCHAR	Constraint number
CONSTRAINT_NAME	VARCHAR	Constraint name
TABLE_NAME	VARCHAR	Name of table with constraint
TABLE_ID	INT64	Number of table withconstraint
COLUMN_NAME	VARCHAR	Name of column with constraint
COLUMN_ID	INT64	Number of column with constraint
COLUMN_POSITION	INT32	Position of column with constraint
RELATED_COLUMN_NAME	VARCHAR	Name of related column in a constraint
RELATED_COLUMN_ID	INT32	Number of related column in a constraint

*_USER_SCHEMAS*		Describes the schemas of the database
SCHEMA_ID	INT32	Schema number
SCHEMA_NAME	VARCHAR	Name of schema
READ_GROUP_ID	INT32	Number of group having read-only access
READ_GROUP_NAME	VARCHAR	Name of group having read-only access
READ_WRITE_GROUP_ID	INT32	Number of group having read-write access
READ_WRITE_GROUP_NAME	VARCHAR	Name of group having read-write access
ALL_GROUP_ID	INT32	Number of group having full access
ALL_GROUP_NAME	VARCHAR	Name of group having full access

_USER_VIEWS		Describes the views of database users
VIEW_NAME	VARCHAR	Name of view
SCHEMA_ID	INT32	ID of schema_name to which the view belongs

_USER_VIEW_COLUMNS		Describes the columns of the views of the database users
VIEW_NAME	VARCHAR	Name of view
COLUMN_NAME	VARCHAR	Name of column
DATA_TYPE	INT32	Type of column (See Data Types)
DATA_LENGTH	INT32	Size of column
NULLABLE	BOOLEAN	True if column accepts NULL values; otherwise, False

Note: The system tables are assigned to a specific schema named SYSTEM_SCHEMA. This schema cannot be modified or deleted. It does not appear in the list of schemas displayed in the table Inspector palette. It can be accessed in read-only by any user.