Returns partitions and dbspace assignments of database objects and subobjects.
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.
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, which 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 its subobjects. The subobjects 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) 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, or table). |
| object_id | Global object ID of the object. |
| id | Table ID of the object. |
| dbspace_name | Name of the dbspace on which the object resides. The string “[multiple]” appears in 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 subobjects 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 subobjects 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)