sp_iqobjectinfo Procedure

Returns partitions and dbspace assignments of database objects and sub-objects.

Syntax

sp_iqobjectinfo [ owner_name ]  [ , object_name ] [ , object-type ]

Permissions

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

Usage

Parameter
Parameter	Description
owner_name	Owner of the object. If specified, sp_iqobjectinfo displays output only for tables and join indexes with the specified owner. If not specified, sp_iqobjectinfo displays information on tables and join indexes for all users in the database.
object_name	Name of the table or join index. If not specified, sp_iqobjectinfo displays information on all tables and join indexes in the database.
object-type	Valid object types are table (the default) or joinindex. If the object-type is a table, it must be enclosed in quotation marks.

All parameters are optional, and any parameter may be supplied independent of the value of another parameter.

Sybase recommends that you use input parameters with sp_iqobjectinfo; you can query the results of the sp_iqobjectinfo and it performs better if you use input parameters rather than using predicates in the WHERE clause of the query. For example, Query A is written as:

SELECT COUNT(*) FROM sp_iqobjectinfo()
WHERE owner = 'DBA'
AND object_name = 'tab_case510'
AND object_type = 'table'
AND sub_object_name is NULL
AND dbspace_name = 'iqmain7'
AND partition_name = 'P1'

Query B is Query A rewritten to use sp_iqobjectinfo input parameters:

SELECT COUNT(*) FROM sp_iqobjectinfo('DBA','tab_case510','table')
WHERE sub_object_name is NULL
AND dbspace_name = 'iqmain7'
AND PARTITION_NAME = 'P1'

Query B returns results faster than Query A. When the input parameters are passed to sp_iqobjectinfo, the procedure compares and joins fewer records in the system tables, thus doing less work compared to Query A. In Query B, the predicates are applied in the procedure itself and the procedure returns a smaller result set, so a smaller number of predicates is applied in the query.

The sp_iqobjectinfo stored procedure supports wildcard characters for interpreting owner_name, object_name, and object_type. It shows information for all dbspaces that match the given pattern in the same way the LIKE clause matches patterns inside queries.

Description

Returns all the partitions and the dbspace assignments of a particular or all database objects (of type table and join index only) and its sub-objects. The sub-objects are columns, indexes, primary key, unique constraints, and foreign keys.

sp_iqobjectinfo columns
Column name	Description
owner	Name of the owner of the object.
object_name	Name of the object (of type table and join index only) located on the dbspace.
sub_object_name	Name of the object located on the dbspace.
object_type	Type of the object (column, index, primary key, unique constraint, foreign key, partition, join index or table).
object_id	Global object id of the object.
id	Table id or join-index id of the object.
dbspace_name	Name of the dbspace on which the object resides. The string “[multiple]” is displayed for a special meta row for partitioned objects. The [multiple] row indicates that multiple rows follow in the output to describe the table or column.
partition_name	Name of the partition for the given object.

Examples

Note: The following two examples show objects in the iqdemo database to better illustrate output. Note that iqdemo includes a sample user dbspace named iq_main that may not be present in your own databases.

Displays information about partitions and dbspace assignments of a specific database object and sub-objects owned by a specific user:

sp_iqobjectinfo GROUPO,Departments

owner   object_name  sub_object_name                object_type  object_id  id 
GROUPO  Departments  (NULL)                          table         3632     738 
GROUPO  Departments  DepartmentID                    column        3633     738 
GROUPO  Departments  DepartmentName                  column        3634     738 
GROUPO  Departments  DepartmentHeadID                column        3635     738 
GROUPO  Departments  DepartmentsKey                  primary key   83       738 
GROUPO  Departments  FK_DepartmentHeadID_EmployeeID  foreign key   92       738 

dbspace_name     partition_name
iq_main          (NULL)
iq_main          (NULL)
iq_main          (NULL)
iq_main          (NULL)
iq_main          (NULL)
iq_main          (NULL)

Displays information about partitions and dbspace assignments of a specific database object and sub-objects owned by a specific user for object-type table:

sp_iqobjectinfo DBA,sale,'table'

owner  object_name  sub_object_name  object_type  object_id  id
DBA    sale         (NULL)            table         3698     742
DBA    sale         prod_id           column        3699     742
DBA    sale         month_num         column        3700     742
DBA    sale         rep_id            column        3701     742
DBA    sale         sales             column        3702     742

dbspace_name      partition_name
iq_main           (NULL)
iq_main           (NULL)
iq_main           (NULL)
iq_main           (NULL)
iq_main           (NULL)