sp_iqhelp procedure

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

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

None required.

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.

• 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 “Object not found” is returned.

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.

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

Sybase IQ has some architectural differences from ASE in terms of types of objects supported and the namespace of objects. In ASE, 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 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 ASE, the types of objects supported by and the syntax of Sybase IQ sp_iqhelp are different from the supported objects and syntax of ASE sp_help; however, the type of information about database objects that is displayed by both stored procedures is similar.

Display detailed information about the table sale:

sp_iqhelp sale

 Table_name Table_owner Server_type Location Remarks  table_constraints
==========  ===========  ==========  ======= == =====  =============                                            sale      DBA         IQ           Main    (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    remarks    check
  ==============     =======    =====
  0                  (NULL)     (NULL)
  0                  (NULL)     (NULL)
  0                  (NULL)     (NULL)
  0                  (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 customer
                           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  remarks
  ======= ========
  (NULL)   (NULL)