Search

Schema Discovery

Version 22.0.8486

Schema Discovery

Version 22.0.8486

Knowing what tables, views and procedures are accessible in a connection is essential to constructing valid SQL queries. You can use the following methods to find what resources can be queried.

Using CData System Tables

CData expose system tables providing you with information on what resources are accessible through SQL queries.

sys_tables

The following query retrieves the available tables:

SELECT * FROM sys_tables

Columns

Name Type Description
CatalogName String The connection containing the table.
SchemaName String The schema containing the table.
TableName String The name of the table.
TableType String The table type.
Description String A description of the table.

sys_views

The following query retrieves the available views:

SELECT * FROM sys_views

Columns

Name Type Description
CatalogName String The connection containing the view.
SchemaName String The schema containing the view.
TableName String The name of the view.
TableType String The view type.
Description String A description of the view.

sys_tablecolumns

The following query returns the columns and data types for the Account table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Account'

Columns

Name Type Description
CatalogName String The connection containing the table.
SchemaName String The schema containing the table.
TableName String The name of the table containing the column.
ColumnName String The column name.
DataTypeName String The data type name.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The length in characters of the column or the numeric precision.
NumericPrecision Int32 The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The column scale or number of digits to the right of the decimal point.
IsNullable Boolean Whether the column can contain null.
Description String A brief description of the column.
Ordinal Int32 The sequence number of the column.
IsAutoIncrement String Whether the column value is assigned in fixed increments.
IsGeneratedColumn String Whether the column is generated.
IsReadOnly Boolean Whether the column is read-only.
IsKey Boolean Whether the column is a primary key.
IsHidden Boolean Whether the column is hidden.

sys_procedures

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures

Columns

Name Type Description
CatalogName String The connection containing the stored procedure.
SchemaName String The schema containing the stored procedure.
ProcedureName String The name of the stored procedure.
Description String A description of the stored procedure.

sys_procedureparameters

The following query returns information about all of the input parameters for the CreateJob stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='CreateJob' AND Direction=1 OR Direction=2

Columns

Name Type Description
CatalogName String The connection containing the stored procedure.
SchemaName String The schema containing the stored procedure.
ProcedureName String The stored procedure containing the parameter.
ColumnName String The stored procedure parameter.
Direction Int32 An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName String The name of the data type.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision Int32 The maximum precision for numeric data. The column length in characters for character and date-time data.
NumbericScale Int32 The number of digits to the right of the decimal point in numeric data.
IsNullable Boolean Whether the parameter can contain null.
Description String The description of the parameter.
Ordinal Int32 The index of the parameter.