Displays the size of each object and subobject used in the specified table or join index.
sp_iqdbspaceinfo [ dbspace-name ] [ , owner_name ] [ , object_name ] [ , object-type ]
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
dbspace_name If specified, sp_iqdbspaceinfo displays one line for each table that has any component in the specified dbspace. Otherwise, the procedure shows information for all dbspaces in the database.
owner_name Owner of the object. If specified, sp_iqdbspaceinfo displays output only for tables and join indexes with the specified owner. If not specified, sp_iqdbspaceinfo 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_iqdbspaceinfo displays information on all tables and join indexes in the database.
object_type Valid object types are table (for table information) or joinindex (for join index information). If unspecified, object type defaults to table.
All parameters are optional and any parameter may be supplied independent of another parameter’s value.
The sp_iqdbspaceinfo stored procedure supports wildcard characters for interpreting dbspace_name, object_name, and owner_name. It shows information for all dbspaces that match the given pattern in the same way the LIKE clause matches patterns inside queries.
sp_iqdbspaceinfo shows the DBA the amount of space used by objects that reside on each dbspace. The DBA can use this information to determine which objects must be relocated before a dbspace can be dropped. The sub-object columns display sizes reported in integer quantities followed by the suffix B, K, M, G, T, or P, representing bytes, kilobytes, megabytes, gigabytes, terabytes, and petabytes, respectively.
For tables, sp_iqdbspaceinfo displays sub-object sizing information for all sub-objects (using integer quantities with the suffix B, K, M, G, T, or P). For join indexes, the procedure displays sizing information for the join index and all of its associated sub-objects. The output is sorted by dbspace_name, object_name, and owner_name.
Column name |
Description |
|---|---|
dbspace_name |
Name of the dbspace. |
object_type |
Type of the object (table or joinindex only). |
owner |
Name of the owner of the object. |
object_name |
Name of the object (of type tables and join indexes only) located on the dbspace. |
object_id |
Global object ID of the object. |
id |
Table id or join-index ID of the object. |
columns |
Size of column storage space on the given dbspace. |
indexes |
Size of index storage space on the given dbspace. Does not use sytem-generated indexes (for example, HG indexes in unique constraints or FP indexes). |
metadata |
Size of storage space for metadata objects on the given dbspace. |
primary_key |
Size of storage space for primary key related objects on the given dbspace. |
unique_constraint |
Size of storage space for unique constraint-related objects on the given dbspace. |
foreign_key |
Size of storage space for foreign-key-related objects on the given dbspace. |
dbspace_online |
Indicates if the dbspace is online (Y) or offline (N). |
Note
If you run sp_iqdbspaceinfo against a server you
have started with the -r switch (read-only), you see the error Msg
13768, Level 14, State 0: SQL Anywhere Error -757: Modifications
not permitted for read-only database. This
behavior is expected. The error does not occur on other stored procedures
such as sp_iqdbspace, sp_iqfile, sp_iqdbspaceobjectinfo or sp_iqobjectinfo.
Displays the size of all objects and sub-objects in all tables in all dbspaces in the database:
The following three 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.
sp_iqdbspaceinfo
dbspace_name object_type owner object_name object_id id columns iq_main table DBA emp1 3689 741 96K iq_main table DBA iq_dummy 3686 740 24K iq_main table DBA sale 3698 742 96K iq_main table GROUPO Contacts 3538 732 288K iq_main table GROUPO Customers 3515 731 240K iq_main table GROUPO Departments 3632 738 72K iq_main table GROUPO Employees 3641 739 408K iq_main table GROUPO FinancialCodes 3612 736 72K iq_main table GROUPO FinancialData 3621 737 96K iq_main table GROUPO Products 3593 735 272K iq_main table GROUPO SalesOrderItems 3580 734 120K iq_main table GROUPO SalesOrders 3565 733 144K indexes metadata primary_key unique_constraint foreign_key dbspace_online 0B 1.37M 0B 0B 0B Y 0B 464K 0B 0B 0B Y 0B 1.22M 0B 0B 0B Y 0B 5.45M 24K 0B 48K Y 48K 4.63M 24K 0B 0B Y 0B 1.78M 24K 0B 48K Y 0B 8.03M 24K 0B 48K Y 0B 1.53M 24K 0B 0B Y 0B 2.19M 24K 0B 48K Y 192K 4.67M 24K 0B 0B Y 0B 2.7M 24K 0B 104K Y 0B 3.35M 24K 0B 144K Y
Displays the size of all objects and sub-objects owned by a specified user in a specified dbspace in the database:
sp_iqdbspaceinfo iq_main,GROUPO
dbspace_name object_type owner object_name object_id id columns iq_main table GROUPO Contacts 3538 732 288K iq_main table GROUPO Customers 3515 731 240K iq_main table GROUPO Departments 3632 738 72K iq_main table GROUPO Employees 3641 739 408K iq_main table GROUPO FinancialCodes 3612 736 72K iq_main table GROUPO FinancialData 3621 737 96K iq_main table GROUPO Products 3593 735 272K iq_main table GROUPO SalesOrderItems 3580 734 120K iq_main table GROUPO SalesOrders 3565 733 144K indexes metadata primary_key unique_constraint foreign_key dbspace_online 0B 5.45M 24K 0B 48K Y 48K 4.63M 24K 0B 0B Y 0B 1.78M 24K 0B 48K Y 0B 8.03M 24K 0B 48K Y 0B 1.53M 24K 0B 0B Y 0B 2.19M 24K 0B 48K Y 192K 4.67M 24K 0B 0B Y 0B 2.7M 24K 0B 104K Y 0B 3.35M 24K 0B 144K Y
Displays the size of a specified object and its sub-objects owned by a specified user in a specified dbspace in the database:
sp_iqdbspaceinfo iq_main,GROUPO,Departments
dbspace_name object_type owner object_name object_id id columns iq_main table GROUPO Departments 3632 738 72K indexes metadata primary_key unique_constraint foreign_key dbspace_online 0B 1.78M 24K 0B 48K Y
“sp_iqdbspace procedure” and “sp_iqindexinfo procedure”
“sp_iqfile procedure” in Appendix A, “Multiplex Reference,” in Using Sybase IQ Multiplex.
