sp_iqhelp procedure

Function

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

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.

**Table 7-27: 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

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.

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.

**Table 7-28: 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.

**Table 7-29: 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.

**Table 7-30: 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

For descriptions of the individual output columns listed in Table 7-30, refer to the descriptions of the following stored procedures:

table: “sp_iqtable procedure”
column: “sp_iqcolumn procedure”
index: “sp_iqindex and sp_iqindex_alt procedures”
constraint: “sp_iqconstraint procedure”
join index: “sp_iqjoinindex procedure”
view: “sp_iqview procedure” and the Adaptive Server Enterprise catalog procedure sp_columns (for view columns)
stored procedure and function: “sp_iqprocedure procedure” and “sp_iqprocparm procedure” (for procedure parameters)
event: “sp_iqevent procedure”
data type: “sp_iqdatatype procedure”

Adaptive Server Enterprise compatibility The Sybase IQ sp_iqhelp stored procedure is similar to the Adaptive Server Enterprise sp_help procedure, which displays information about any database object listed in the SYSOBJECTS system table and about system and user-defined data types.

Sybase IQ has some architectural differences from Adaptive Server in terms of types of objects supported and the namespace of objects. In Adaptive Server, all objects (tables, views, stored procedures, logs, rules, defaults, triggers, check constraints, referential constraints, and temporary objects) are stored in the SYSOBJECTS system table and are in the same namespace. The objects supported by Sybase IQ (tables, views, stored procedures, events, primary keys, and unique, check, and referential constraints) are stored in different system tables and are in different namespaces. For example, in Sybase IQ a table can have the same name as an event or a stored procedure.

Because of the architectural differences between Sybase IQ and Adaptive Server, the types of objects supported by and the syntax of Sybase IQ sp_iqhelp are different from the supported objects and syntax of Adaptive Server sp_help; however, the type of information about database objects that is displayed by both stored procedures is similar.

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)