Displays columns in a database and information about them.
sp_iqcolumn ( [ table_name ],[ table_owner ] )
sp_iqcolumn [ table_name='tablename' ],[ table_owner='tableowner' ]
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. Be sure to enclose 'tablename' and 'tableowner' in single quotes.
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 parameters chooses the columns from a unique table, if that table exists. Specifying no parameters returns all columns for all tables in a database. This procedure 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 Temp Store, MAIN = IQ Store, LOCAL = IQ Local Store, SYSTEM = Catalog Store |
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 department:
sp_iqcolumn department
call sp_iqcolumn (table_name='department')
table_ name |
table_ owner |
column_ name |
domain_name |
width |
scale |
nulls |
default |
cardi-nality |
est_ cardi-nality |
location |
remarks |
check |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
department |
DBA |
dept_id |
unsigned int |
4 |
0 |
N |
N |
5 |
5 |
(NULL) |
(NULL) |
|
department |
DBA |
dept_ name |
char |
40 |
0 |
N |
N |
0 |
5 |
(NULL) |
(NULL) |
|
department |
DBA |
dept_head_id |
unsigned int |
4 |
0 |
Y |
N |
5 |
5 |
(NULL) |
(NULL) |
The following variations in syntax both return all of the columns in all of the tables owned by table owner DBA. For brevity, some rows have been omitted from the results shown:
sp_iqcolumn table_owner='DBA'
sp_iqcolumn NULL,DBA
table_ name |
table_ owner |
column _name |
domain _name |
width |
scale |
nulls |
default |
cardi-nality |
est_ cardi- nality |
location |
remarks |
check |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
contact |
DBA |
id |
unsigned int |
4 |
0 |
N |
(NULL) |
60 |
60 |
(NULL) |
(NULL) |
|
contact |
DBA |
last_ name |
char |
15 |
0 |
N |
(NULL) |
0 |
60 |
(NULL) |
(NULL) |
|
... |
... |
... |
... |
... |
... |
... |
(NULL) |
... |
... |
... |
... |
... |
contact |
DBA |
phone |
char |
10 |
0 |
Y |
(NULL) |
0 |
59 |
(NULL) |
(NULL) |
|
contact |
DBA |
fax |
char |
10 |
0 |
Y |
(NULL) |
0 |
58 |
(NULL) |
(NULL) |
|
customer |
DBA |
id |
unsigned int |
4 |
0 |
N |
(NULL) |
126 |
126 |
(NULL) |
(NULL) |
|
customer |
DBA |
fname |
char |
15 |
0 |
N |
(NULL) |
0 |
116 |
(NULL) |
(NULL) |
|
... |
... |
... |
... |
... |
... |
... |
... |
.. |
... |
... |
... |
... |
customer |
DBA |
phone |
char |
12 |
0 |
N |
(NULL) |
0 |
117 |
(NULL) |
(NULL) |
|
customer |
DBA |
company _name |
char |
35 |
0 |
Y |
(NULL) |
0 |
126 |
(NULL) |
(NULL) |
|
department |
DBA |
dept_id |
unsigned int |
4 |
0 |
N |
(NULL) |
5 |
5 |
(NULL) |
(NULL) |
|
department |
DBA |
dept_ name |
char |
40 |
0 |
N |
(NULL) |
0 |
5 |
(NULL) |
(NULL) |
|
department |
DBA |
dept_ head_id |
unsigned int |
4 |
0 |
Y |
(NULL) |
5 |
5 |
(NULL) |
(NULL) |
|
... |
... |
... |
... |
... |
... |
... |
... |
... |
... |
... |
... |
... |
