Displays information about columns in a database.
sp_iqcolumn ( [ table_name ],[ table_owner ], [table_loc] )
sp_iqcolumn [ table_name='table_name' ],[ table_owner='tableowner' ],[table_loc='table_loc’]
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.
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
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.
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 |
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)
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'
