4D v16

System Tables

Home

 
4D v16
System Tables

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_TABLESDescribes the user tables of the database
TABLE_NAMEVARCHARTable name
TEMPORARYBOOLEANTrue if the table is temporary; otherwise, false
TABLE_IDINT64Table number
SCHEMA_IDINT32Number of schema
REST_AVAILABLEBOOLEANTrue if column is exposed with REST service; otherwise, False
LOGGEDBOOLEANTrue if table operations are included in log file; otherwise, False

_USER_COLUMNSDescribes the columns of the user tables of the database
TABLE_NAMEVARCHARTable name
COLUMN_NAMEVARCHARColumn name
DATA_TYPEINT32Column type
DATA_LENGTHINT32Column length
NULLABLEBOOLEANTrue if column accepts NULL values; otherwise, false
TABLE_IDINT64Table number
COLUMN_IDINT64Column number
UNIQUENESSBOOLEANTrue if column is declared Unique; otherwise, False
AUTOGENERATEBOOLEANTrue if column value is generated automatically for each new record; otherwise, False
AUTOINCREMENTBOOLEANTrue if column value is incremented automatically; otherwise, False
REST_AVAILABLEBOOLEANTrue if column is exposed with REST service; otherwise, False

_USER_INDEXESDescribes the user indexes of the database
INDEX_IDVARCHARIndex number
INDEX_NAMEVARCHARIndex name
INDEX_TYPEINT32Index type (1=BTree / Composite, 3=Cluster / Keyword, 7=Auto, 8=Auto for Object type field)
KEYWORDBOOLEANTrue if index is a keyword index; otherwise, False
TABLE_NAMEVARCHARName of table with index
UNIQUENESSBOOLEANTrue if index imposes a uniqueness constraint; otherwise, false
TABLE_IDINT64Number of table with index

_USER_IND_COLUMNSDescribes the columns of user indexes of the database
INDEX_IDVARCHARIndex number
INDEX_NAMEVARCHARIndex name
TABLE_NAMEVARCHARName of table with index
COLUMN_NAMEVARCHARName of column with index
COLUMN_POSITIONINT32Position of column in index
TABLE_IDINT64Number of table with index
COLUMN_IDINT64Column number

_USER_CONSTRAINTSDescribes the integrity constraints of the database
CONSTRAINT_IDVARCHARConstraint number
CONSTRAINT_NAMEVARCHARName associated with constraint definition
CONSTRAINT_TYPEVARCHARType of constraint definition (P=primary key, R=referential integrity - foreign key, 4DR=4D relation)
TABLE_NAMEVARCHARName of table with constraint definition
TABLE_IDINT64Number of table with constraint
DELETE_RULEVARCHARDelete rule for a referential constraint – CASCADE or RESTRICT
RELATED_TABLE_NAMEVARCHARName of related table
RELATED_TABLE_IDINT64Number of related table

_USER_CONS_COLUMNSDescribes the columns of user constraints of the database
CONSTRAINT_IDVARCHARConstraint number
CONSTRAINT_NAMEVARCHARConstraint name
TABLE_NAMEVARCHARName of table with constraint
TABLE_IDINT64Number of table withconstraint
COLUMN_NAMEVARCHARName of column with constraint
COLUMN_IDINT64Number of column with constraint
COLUMN_POSITIONINT32Position of column with constraint
RELATED_COLUMN_NAMEVARCHARName of related column in a constraint
RELATED_COLUMN_IDINT32Number of related column in a constraint

_USER_SCHEMASDescribes the schemas of the database
SCHEMA_IDINT32Schema number
SCHEMA_NAMEVARCHARName of schema
READ_GROUP_IDINT32Number of group having read-only access
READ_GROUP_NAMEVARCHARName of group having read-only access
READ_WRITE_GROUP_IDINT32Number of group having read-write access
READ_WRITE_GROUP_NAMEVARCHARName of group having read-write access
ALL_GROUP_IDINT32Number of group having full access
ALL_GROUP_NAMEVARCHARName of group having full access

_USER_VIEWSDescribes the views of database users
VIEW_NAMEVARCHARName of view
SCHEMA_IDINT32ID of schema_name to which the view belongs

_USER_VIEW_COLUMNSDescribes the columns of the views of the database users
VIEW_NAMEVARCHARName of view
COLUMN_NAMEVARCHARName of column
DATA_TYPEINT32Type of column
DATA_LENGTHINT32Size of column
NULLABLEBOOLEANTrue 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.

 
EIGENSCHAFTEN 

Produkt: 4D
Thema: Using SQL in 4D

 
GESCHICHTE 

 
ARTIKELVERWENDUNG

4D - SQL Reference ( 4D v16)