@SystemCatalog

Documentation

VoltDB Home » Documentation » Using VoltDB

@SystemCatalog

@SystemCatalog — Returns metadata about the database schema.

Synopsis

@SystemCatalog String component

Description

The @SystemCatalog system procedure returns information about the schema of the VoltDB database, depending upon the component keyword you specify. The following are the allowable values of component:

"COLUMNS"

Returns a list of columns for all of the tables in the database.

"FUNCTIONS"

Returns information about user-defined functions in the database.

"INDEXINFO"

Returns information about the indexes in the database schema. Note that the procedure returns information for each column in the index. In other words, if an index is composed of three columns, the result set will include three separate entries for the index, one for each column.

"PRIMARYKEYS"

Returns information about the primary keys in the database schema. Note that the procedure returns information for each column in the primary key. If an primary key is composed of three columns, the result set will include three separate entries.

"PROCEDURECOLUMNS"

Returns information about the arguments to the stored procedures.

"PROCEDURES"

Returns information about the stored procedures defined in the database.

"ROLES"

Returns information about the roles defined in the database and their associated permissions.

"TABLES"

Returns information about the tables in the database.

"USERS"

Returns information about the users defined in the database and their associated roles.

Return Values

Returns a different VoltTable for each component. The layout of the VoltTables is designed to match the corresponding JDBC data structures. Columns are provided for all JDBC properties, but where VoltDB has no corresponding element the column is unused and a null value is returned.

For the COLUMNS component, the VoltTable has the following columns:

NameDatatypeDescription
TABLE_CATSTRINGUnused.
TABLE_SCHEMSTRINGUnused.
TABLE_NAMESTRINGThe name of the database table the column belongs to.
COLUMN_NAMESTRINGThe name of the column.
DATA_TYPEINTEGERAn enumerated value specifying the corresponding Java SQL datatype of the column.
TYPE_NAMESTRINGA string value specifying the datatype of the column.
COLUMN_SIZEINTEGERThe length of the column in bits, characters, or digits, depending on the datatype.
BUFFER_LENGTHINTEGERUnused.
DECIMAL_DIGITSINTEGERThe number of fractional digits in a DECIMAL datatype column. (Null for all other datatypes.)
NUM_PREC_RADIXINTEGERSpecifies the radix, or numeric base, for calculating the column size. A radix of 2 indicates the column size is measured in bits while a radix of 10 indicates a measurement in bytes or digits.
NULLABLEINTEGERIndicates whether the column value can be null (1) or not (0).
REMARKSSTRINGContains the string "PARTITION_COLUMN" if the column is the partitioning key for a partitioned table. Otherwise null.
COLUMN_DEFSTRINGThe default value for the column.
SQL_DATA_TYPEINTEGERUnused.
SQL_DATETIME_SUBINTEGERUnused.
CHAR_OCTET_LENGTHINTEGERFor variable length columns (VARCHAR and VARBINARY), the maximum length of the column. Null for all other datatypes.
ORDINAL_POSITIONINTEGERAn index specifying the position of the column in the list of columns for the table, starting at 1.
IS_NULLABLESTRINGSpecifies whether the column can contain a null value ("YES") or not ("NO").
SCOPE_CATALOGSTRINGUnused.
SCOPE_SCHEMASTRINGUnused.
SCOPE_TABLESTRINGUnused.
SOURCE_DATE_TYPESMALLINTUnused.
IS_AUTOINCREMENTSTRINGSpecifies whether the column is auto-incrementing or not. (Always returns "NO").

For the FUNCTIONS component, the VoltTable has the following columns:

NameDatatypeDescription
FUNCTION_TYPESTRINGThe function type is always "scalar".
FUNCTION_NAMESTRINGThe name of the user-defined function.
CLASS_NAMESTRINGThe Java class name that contains the user-defined function method.
METHOD_NAMESTRINGThe name of the method that implements the user-defined function.

For the INDEXINFO component, the VoltTable has the following columns:

NameDatatypeDescription
TABLE_CATSTRINGUnused.
TABLE_SCHEMSTRINGUnused.
TABLE_NAMESTRINGThe name of the database table the index applies to.
NON_UNIQUETINYINTValue specifying whether the index is unique (0) or not (1).
INDEX_QUALIFIERSTRINGUnused.
INDEX_NAMESTRINGThe name of the index that includes the current column.
TYPESMALLINTA value indicating the type of index, which is always three (3).
ORDINAL_POSITIONSMALLINTAn index specifying the position of the column in the index, starting at 1.
COLUMN_NAMESTRINGThe name of the column.
ASC_OR_DESCSTRINGA string value specifying the sort order of the index. Possible values are "A" for ascending or null for unsorted indexes.
CARDINALITYINTEGERUnused.
PAGESINTEGERUnused.
FILTER_CONDITIONSTRINGUnused.

For the PRIMARYKEYS component, the VoltTable has the following columns:

NameDatatypeDescription
TABLE_CATSTRINGUnused.
TABLE_SCHEMSTRINGUnused.
TABLE_NAMESTRINGThe name of the database table.
COLUMN_NAMESTRINGThe name of the column in the primary key.
KEY_SEQSMALLINTAn index specifying the position of the column in the primary key, starting at 1.
PK_NAMESTRINGThe name of the primary key.

For the PROCEDURECOLUMNS component, the VoltTable has the following columns:

NameDatatypeDescription
PROCEDURE_CATSTRINGUnused.
PROCEDURE_SCHEMSTRINGUnused.
PROCEDURE_NAMESTRINGThe name of the stored procedure.
COLUMN_NAMESTRINGThe name of the procedure parameter.
COLUMN_TYPESMALLINTAn enumerated value specifying the parameter type. Always returns 1, corresponding to procedureColumnIn.
DATA_TYPEINTEGERAn enumerated value specifying the corresponding Java SQL datatype of the column.
TYPE_NAMESTRINGA string value specifying the datatype of the parameter.
PRECISIONINTEGERThe length of the parameter in bits, characters, or digits, depending on the datatype.
LENGTHINTEGERThe length of the parameter in bytes. For variable length datatypes (VARCHAR and VARBINARY), this value specifies the maximum possible length.
SCALESMALLINTThe number of fractional digits in a DECIMAL datatype parameter. (Null for all other datatypes.)
RADIXSMALLINTSpecifies the radix, or numeric base, for calculating the precision. A radix of 2 indicates the precision is measured in bits while a radix of 10 indicates a measurement in bytes or digits.
NULLABLESMALLINTUnused.
REMARKSSTRINGIf this column contains the string "PARTITION_PARAMETER", the parameter is the partitioning key for a single-partitioned procedure. If the column contains the string "ARRAY_PARAMETER" the parameter is a native Java array. Otherwise this column is null.
COLUMN_DEFSTRINGUnused.
SQL_DATA_TYPEINTEGERUnused.
SQL_DATETIME_SUBINTEGERUnused.
CHAR_OCTET_LENGTHINTEGERFor variable length columns (VARCHAR and VARBINARY), the maximum length of the column. Null for all other datatypes.
ORDINAL_POSITIONINTEGERAn index specifying the position in the parameter list for the procedure, starting at 1.
IS_NULLABLESTRINGUnused.
SPECIFIC_NAMESTRINGSame as COLUMN_NAME

For the PROCEDURES component, the VoltTable has the following columns:

NameDatatypeDescription
PROCEDURE_CATSTRINGUnused.
PROCEDURE_SCHEMSTRINGUnused.
PROCEDURE_NAMESTRINGThe name of the stored procedure.
RESERVED1STRINGUnused.
RESERVED2STRINGUnused.
RESERVED3STRINGUnused.
REMARKSSTRINGUnused.
PROCEDURE_TYPESMALLINTAn enumerated value that specifies the type of procedure. Always returns zero (0), indicating "unknown".
SPECIFIC_NAMESTRINGSame as PROCEDURE_NAME.

For the ROLES component, the VoltTable has the following columns:

NameDatatypeDescription
ROLESTRINGThe name of the role.
PERMISSIONSSTRINGA comma-separated list of permissions associated with the role.

For the TABLES component, the VoltTable has the following columns:

NameDatatypeDescription
TABLE_CATSTRINGUnused.
TABLE_SCHEMSTRINGUnused.
TABLE_NAMESTRINGThe name of the database table.
TABLE_TYPESTRINGSpecifies whether the table is a data table ("TABLE"), a materialized view ("VIEW"), or a stream ('EXPORT").
REMARKSSTRINGUnused.
TYPE_CATSTRINGUnused.
TYPE_SCHEMSTRINGUnused.
TYPE_NAMESTRINGUnused.
SELF_REFERENCING​_COL_NAMESTRINGUnused.
REF_GENERATIONSTRINGUnused.

For the USERS component, the VoltTable has the following columns:

NameDatatypeDescription
USERSTRINGThe name of the user.
ROLESSTRINGA comma-separated list of roles assigned to the user.

Examples

The following example calls @SystemCatalog to list the stored procedures in the active database schema:

$ sqlcmd
1> exec @SystemCatalog procedures;

The next program example uses @SystemCatalog to display information about the tables in the database schema.

VoltTable[] results = null;
try {
     results = client.callProcedure("@SystemCatalog",
               "TABLES").getResults();
     System.out.println("Information about the database schema:");
     for (VoltTable node : results) System.out.println(node.toString());
}
catch (Exception e) {
     e.printStackTrace();
}