sp_iqcolumn procedure

Function

Displays information about columns in a database.

Syntax1

sp_iqcolumn ( [ table_name ],[ table_owner ], [table_loc] )

Syntax2

sp_iqcolumn [ table_name='table_name' ],[ table_owner='tableowner' ],[table_loc='table_loc’]

Usage

Syntax1 If you specify table_owner without specifying table_name, you must substitute NULL for table_name. For example, sp_iqcolumn NULL,DBA.

Syntax2 The parameters can be specified in any order. Enclose 'table_name' and 'table_owner' in single quotes.

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Description

Displays information about columns in a database. Specifying the table_name parameter returns the columns only from tables with that name. Specifying the table_owner parameter returns only tables owned by that user. Specifying both table_name and table_owner parameters chooses the columns from a unique table, if that table exists. Specifying table_loc returns only tables that are defined in that segment type. Specifying no parameters returns all columns for all tables in a database. sp_iqcolumn does not return column information for system tables.

Table 7-6: sp_iqcolumn columns

Column name

Description

table_name

The name of the table

table_owner

The owner of the table

column_name

The name of the column

domain_name

The data type

width

The precision of numeric data types that have precision and scale or the storage width of numeric data types without scale; the width of character data types

scale

The scale of numeric data types

nulls

'Y' if the column can contain NULLS, 'N' if the column cannot contain NULLS

default

'Identity/Autoincrement' if the column is an identity/autoincrement column, null if not.

cardinality

The distinct count, if known, by indexes

est_cardinality

The estimated number of distinct values, set to 255 automatically if the column was created with the MINIMIZE_STORAGE option ON, or a user-supplied value from the IQ UNIQUE constraint specified in CREATE TABLE

location

TEMP = IQ temporary store, MAIN = IQ main store, SYSTEM = catalog store

isPartitioned

'Y' if the column belongs to a partitioned table and has one or more partitions whose dbspace is different from the table partition’s dbspace, 'N' if the column’s table is not partitioned or each partition of the column resides in the same dbspace as the table partition.

remarks

User comments added with the COMMENT statement

check

the check constraint expression

Example

The following variations in syntax both return all of the columns in the table Departments:

sp_iqcolumn Departments
call sp_iqcolumn (table_name='Departments')

table_name   table_owner  column_name     domain_name  width  scale  nulls  default
Departments  GROUPO       DepartmentID    integer       4        0   N      (NULL)
Departments  GROUPO       DepartmentName  char         40        0   N      (NULL)
Departments  GROUPO       DepartmentHead  integer       4        0   Y      (NULL)

cardinality  est_cardinality  location  isPartitioned  remarks  check
5             5               Main       N             (NULL)   (NULL)
0             5               Main       N             (NULL)   (NULL)
5             5               Main       N             (NULL)   (NULL)

Example 7

The following variation in syntax returns all of the columns in all of the tables owned by table owner DBA.

sp_iqcolumn table_owner='DBA'