Search

Metadata

Metadata

This page lists the available methods for discovering the tables, views, and procedures that are available in your connections.

sys_tables

The following query retrieves the available tables:

SELECT * FROM sys_tables

Columns

Name Type Description
CatalogName String The connection that contains the table
SchemaName String The schema that contains 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 that contains the view
SchemaName String The schema that contains 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 a table named Account:

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

Columns

Name Type Description
CatalogName String The connection that contains the table
SchemaName String The schema that contains the table
TableName String The name of the table that contains the column
ColumnName String The column name
DataTypeName String The data type name
DataType Int32 An integer that indicates the data type. This value is determined at run time based on the environment.
Length Int32 The length (number of characters) of the column or the numeric precision
NumericPrecision Int32 The maximum number of digits in numeric data. The column length (number of 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 that contains the stored procedure
SchemaName String The schema that contains 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 a stored procedure named CreateJob:

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

Columns

Name Type Description
CatalogName String The connection that contains the stored procedure
SchemaName String The schema that contains the stored procedure
ProcedureName String The stored procedure that contains the parameter
ColumnName String The stored procedure parameter
Direction Int32 An integer that corresponds 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 that indicates 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