Returns partitions and dbspace assignments of database objects and sub-objects.
sp_iqobjectinfo [ owner_name ] [ , object_name ] [ , object-type ]
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
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.
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.
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. |
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)
