Lists objects and subobjects of type table (including columns, indexes, metadata, primary keys, unique constraints, foreign keys, and partitions) for a given dbspace. Not supported for RLV dbspaces.
All parameters are optional and any parameter may be supplied independent of the value of other parameters.
The sp_iqdbspaceobjectinfo stored procedure supports wildcard characters for interpreting dbspace_name, object_name, and owner_name. It displays information for all dbspaces that match the given pattern in the same way as the LIKE clause matches patterns inside queries.
The procedure returns no results if you specify an RLV dbspace.
For tables, sp_iqdbspaceobjectinfo displays summary information for all associated subobjects sorted by dbspace_name, owner and object_name.
sp_iqdbspaceobjectinfo displays the following information, based on the input parameter values:
Column Name | Description |
---|---|
dbspace_name | Name of the dbspace. |
dbspace_id | Identifier of the dbspace. |
object_type | Table. |
owner | Name of the owner of the object. |
object_name | Name of the table object on the dbspace. |
object_id | Global object ID of the object. |
id | Table ID of the object. |
columns | Number of table columns which are located on the given dbspace. If a column or one of the column-partitions is located on a dbspace, it is counted to be present on that dbspace. The result is shown in the form n/N (n out of total N columns of the table are on the given dbspace). |
indexes | Number of user-defined indexes on the table which are located on the given dbspace. Shown in the form n/N (n out of total N indexes on the table are on the given dbspace). This does not contain indexes which are system-generated, such as FP indexes and HG indexes in the case of unique constraints. |
metadata | Boolean field (Y/N) that denotes whether the metadata information of the subobject is also located on this dbspace. |
primary_key | Boolean field (1/0) that denotes whether the primary key of the table, if any, is located on this dbspace. |
unique_constraint | Number of unique constraints on the table that are located on the given dbspace. Appears in the form n/N (n out of total N unique constraints on the table are in the given dbspace). |
foreign_key | Number of foreign_keys on the table that are located on the given dbspace. Appears in the form n/N (n out of total N foreign keys on the table are in the given dbspace). |
partitions | Number of partitions of the table that are located on the given dbspace. Appears in the form n/N (n out of total N partitions of the table are in the given dbspace). |
These examples show objects in the iqdemo database to better illustrate output. iqdemo includes a sample user dbspace named iq_main that may not be present in your own databases.
Displays information about a specific dbspace in the database:
sp_iqdbspaceobjectinfo iq_main
dbspace_name dbspace_id object_type owner object_name object_id id columns iq_main 16387 table DBA emp1 3689 741 4/4 iq_main 16387 table DBA iq_dummy 3686 740 1/1 iq_main 16387 table DBA sale 3698 742 4/4 iq_main 16387 table GROUPO Contacts 3538 732 12/12 iq_main 16387 table GROUPO Customers 3515 731 10/10 iq_main 16387 table GROUPO Departments 3632 738 3/3 iq_main 16387 table GROUPO Employees 3641 739 21/21 iq_main 16387 table GROUPO FinancialCodes 3612 736 3/3 iq_main 16387 table GROUPO FinancialData 3621 737 4/4 iq_main 16387 table GROUPO Products 3593 735 8/8 iq_main 16387 table GROUPO SalesOrderItems3580 734 5/5 iq_main 16387 table GROUPO SalesOrders 3565 733 6/6 indexes metadata primary_key unique_constraint foreign_key partitions 0/0 Y 0 0/0 0/0 0/0 0/0 Y 0 0/0 0/0 0/0 0/0 Y 0 0/0 0/0 0/0 0/0 Y 1 0/0 1/1 0/0 1/1 Y 1 0/0 0/0 0/0 0/0 Y 1 0/0 1/1 0/0 0/0 Y 1 0/0 1/1 0/0 0/0 Y 1 0/0 0/0 0/0 0/0 Y 1 0/0 1/1 0/0 4/4 Y 1 0/0 0/0 0/0 0/0 Y 1 0/0 2/2 0/0 0/0 Y 1 0/0 3/3 0/0
Displays information about the objects owned by a specific user in a specific dbspace in the database:
sp_iqdbspaceobjectinfo iq_main,GROUPO
dbspace_name dbspace_id object_type owner object_name object_id id columns iq_main 16387 table GROUPO Contacts 3538 732 2/12 iq_main 16387 table GROUPO Customers 3515 731 10/10 iq_main 16387 table GROUPO Departments 3632 738 3/3 iq_main 16387 table GROUPO Employees 3641 739 21/21 iq_main 16387 table GROUPO FinancialCodes 3612 736 3/3 iq_main 16387 table GROUPO FinancialData 3621 737 4/4 iq_main 16387 table GROUPO Products 3593 735 8/8 iq_main 16387 table GROUPO SalesOrderItems3580 734 5/5 iq_main 16387 table GROUPO SalesOrders 3565 733 6/6 indexes metadata primary_key unique_constraint foreign_key partitions 0/0 Y 1 0/0 1/1 0/0 1/1 Y 1 0/0 0/0 0/0 0/0 Y 1 0/0 1/1 0/0 0/0 Y 1 0/0 1/1 0/0 0/0 Y 1 0/0 0/0 0/0 0/0 Y 1 0/0 1/1 0/0 4/4 Y 1 0/0 0/0 0/0 0/0 Y 1 0/0 2/2 0/0 0/0 Y 1 0/0 3/3 0/0
In this example, the commands move all tables on dbspace_x to dbspace_y.
SELECT 'ALTER TABLE ' || owner || '.' || object_name || ' MOVE TO dbspace_y;' FROM sp_iqdbspaceobjectinfo() WHERE object_type = 'table' AND dbspace_name = 'dbspace_x';
The following ALTER TABLE commands are the result:
ALTER TABLE DBA.dt1 MOVE TO dbspace_y; ALTER TABLE DBA.dt2 MOVE TO dbspace_y; ALTER TABLE DBA.dt3 MOVE TO dbspace_y;