sp_iqhelp procedure

Function

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

Syntax

sp_iqhelpobj-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:

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:

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)