sp_iqhelp Procedure

Displays information about system and user-defined objects and data types.

Syntax

sp_iqhelp [ obj-name ], [ obj-owner ], [ obj-category ], [ obj-type ]

Permissions

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

Usage

Parameters
Parameter	Description
obj-name	The name of the object.
obj-owner	The owner of the object.
obj-category	An optional parameter that specifies the category of the object. Columns, constraints, and indexes are associated with tables and cannot be queried directly. When a table is queried, the information about columns, indexes, and constraints associated with that table is displayed. If the specified object category is not one of the allowed values, an `“Invalid object category”` error is returned.
obj-type	The type of object. Allowed values are: SYSTEM: displays information about system objects (objects owned by user `SYS` or `dbo`) only ALL: displays information about all objects By default, only information about non-system objects is displayed. If the specified object type is not SYSTEM or ALL, an `“Invalid object type”` error is returned.

sp_iqhelp obj-category parameter values
`object-type` parameter	Specifies
“table”	The object is a base table
“view”	The object is an view
“procedure”	The object is a stored procedure or function
“event”	The object is an event
“datatype”	The object is a system or user-defined data type

The sp_iqhelp procedure can be invoked without any parameters. If no parameters are specified, sp_iqhelp displays information about all independent objects in the database, that is, base tables, views, stored procedures, functions, events, and data types.

If you do not specify any of the first three parameters, but specify the next parameter in the sequence, you must substitute NULL for the omitted parameters. For example, sp_iqhelp NULL, NULL, NULL, SYSTEM and sp_iqhelp NULL, user1, “table”.

Enclose the obj-category parameter in single or double quotes., except when NULL.

If sp_iqhelp does not find an object in the database that satisfies the specified description, the error “No object found for the given description” is returned.

sp_iqhelp usage examples
Syntax	Output
sp_iqhelp	Displays summary information about all user-defined tables, views, procedures, events, and data types in the database
sp_iqhelp t1, u1, “table”	Displays information about table `t1` owned by user `u1` and the columns, indexes, and constraints associated with `t1`
sp_iqhelp NULL, u1, “view”	Displays information about view `v1` owned by user `u1` and the columns associated with `v1`
sp_iqhelp sp2	Displays information about the procedure `sp2` and the parameters of `sp2`
sp_iqhelp e1	Displays information about the event `e1`
sp_iqhelp dt1	Displays information about the data type `dt1`
sp_iqhelp NULL, NULL, NULL, SYSTEM	Displays summary information about all system objects (owned by `dbo` or `SYS`)
sp_iqhelp non_existing_obj	Error `“Object ‘non_existing_obj’ not found”` returned, as the object non_existing_obj does not exist
sp_iqhelp NULL, non_existing_user	Error `“User ‘non_existing_user’ not found”` returned, as the user non_existing_user does not exist
sp_iqhelp t1, NULL, “apple”	Error`“Invalid object category ‘apple’”` returned, as “apple” is not an allowed value for `obj-category`
sp_iqhelp t1, NULL, NULL, “USER”	Error`“Invalid object type ‘USER’”` returned, as “USER” is not an allowed value for `obj-type`

Description

The sp_iqhelp stored procedure displays information about system and user-defined objects and data types in an IQ database. Objects supported by sp_iqhelp are tables, views, columns, indexes, join indexes, constraints, stored procedures, functions, events, and data types.

If you specify one or more parameters, the result is filtered by the specified parameters. For example, if obj-name is specified, only information about the specified object is displayed. If obj-owner is specified, sp_iqhelp returns information only about objects owned by the specified owner. If no parameters are specified, sp_iqhelp displays summary information about all user-defined tables, views, procedures, events, and data types in the database.

The sp_iqhelp procedure returns either summary or detailed information, depending on whether the specified parameters match multiple objects or a single object. The output columns of sp_iqhelp are similar to the columns displayed by the stored procedures sp_iqtable, sp_iqindex, sp_iqview, and sp_iqconstraint.

When multiple objects match the specified sp_iqhelp parameters, sp_iqhelp displays summary information about those objects.

sp_iqhelp summary information
Object type	Columns displayed
base table	table_name, table_owner, server_type, location, table_constraints, remarks
view	view_name, view_creator, view_def, server_type, location, remarks
stored procedure	proc_name, proc_creator, proc_defn, replicate, srvid, remarks
function	proc_name, proc_creator, proc_defn, replicate, remarks
event	event_name, event_creator, enabled, location, event_type, action, external_action, condition, remarks
system and user-defined data types	type_name, creator, nulls, width, scale, default, check

When a single object matches the specified sp_iqhelp parameters, sp_iqhelp displays detailed information about the object.

sp_iqhelp detailed information
Object type	Description	Columns
table	Displays information about the specified base table, its columns, indexes, constraints, and join indexes (if the table participates in any join indexes)	Table columns: table_name, table_owner, server_type, location, table_constraints, remarks Column columns: column_name, domain_name, width, scale, nulls, default, check, pkey, user_type, cardinality, est_cardinality, remarks Index columns: index_name, column_name, index_type, unique_index, location, remarks Constraint columns: constraint_name (role), column_name, index_name, constraint_type, foreigntable_name, foreigntable_owner, foreigncolumn_name, foreignindex_name, location Join index columns: joinindex_name, creator, left_table_name, left_table_owner, left_column_name, join_type, right_table_name, right_table_owner, right_column_name, key_type, valid, remarks
view	Displays information about the specified view and its columns	View columns: view_name, view_creator, view_def, server_type, location, remarks Column columns: column_name, domain_name, width, scale, nulls, default, check, pkey, user_type, cardinality, est_cardinality, remarks
stored procedure	Displays information about the specified procedure and its parameters	Procedure columns: proc_name, proc_creator, proc_defn, replicate, srvid, remarks Parameter columns: parameter_name, type, width, scale, default, mode
function	Displays information about the specified function and its parameters	Function columns: proc_name, proc_creator, proc_defn, replicate, srvid, remarks Parameter columns: parameter_name, type, width, scale, default, mode
event	Displays information about the specified event	Event columns: event_name, event_creator, enabled, location, event_type, action, external_action, condition, remarks
data type	Displays information about the specified data type	Data type columns: type_name, creator, nulls, width, scale, default, check

Note: Procedure definitions (proc-defn) of system procedures are encrypted and hidden from view.

For descriptions of the individual output columns, refer to the related stored procedure. For example, for a description of the table column, see the sp_iqtable procedure.

Examples

Display detailed information about the table sale:

sp_iqhelp sale

 Table_name Table_owner Server_type Location dbspace_id isPartitioned  table_constraints
==========  ===========  ==========  =======  == ======= =============                                            sale      DBA         IQ           Main    16387      N

Remarks  table_constraints
=======  ================== (NULL)   (NULL) 

column_name domain_name width scale nulls default cardinality
==========  =========== ===== ===== ===== ======= ===========
prod_id     integer     4     0     Y    (NULL)   0
month_num   integer     4     0     Y    (NULL)   0
rep_id      integer     4     0     Y    (NULL)   0
sales       integer     4     0     Y    (NULL)   0

  est_cardinality    isPartitioned    remarks    check
  ==============     =============    =======    =====
  0                  N               (NULL)      (NULL)
  0                  N               (NULL)      (NULL)
  0                  N               (NULL)      (NULL)
  0                  N               (NULL)      (NULL)

index_name           column_name  index_type  unique_index  location
==========           ===========  =========== ===========   ========
ASIQ_IDX_T463_C2_FP  month_num    FP          N             Main
ASIQ_IDX_T463_C1_FP  prod_id      FP          N             Main
ASIQ_IDX_T463_C3_FP  rep_id       FP          N             Main
ASIQ_IDX_T463_C4_FP  sales        FP          N             Main

  remarks
  =======
  (NULL)
  (NULL)
  (NULL)
  (NULL)

Display detailed information about the procedure sp_customer_list:

sp_iqhelp sp_customer_list
proc_name    proc_owner    proc_defn
==========  ===========    =========
sp_customer_list    DBA    create procedure DBA.sp_customer_list()
                           result(id integer company_name char(35))
                           begin
                           select id company_name from Customers
                           end

  replicate    srvid    remarks
  =========    =====    =======
  N            (NULL)   (NULL)

parm_name      parm_type  parm_mode  domain_name  width  scale
=========      =========  =========  ===========  =====  =====
id             result     out        integer      4      0
company_name   result     out        char         35     0

  default
  =======
  (NULL)