Displays the size of each object and subobject used in the specified table. Not supported for RLV dbspaces.
Simplex and multiplex.
Requires the BACKUP DATABASE, SERVER OPERATOR, or MANAGE ANY DBSPACE system privileges. Users without one of these system privileges must be granted EXECUTE permission.
Parameter |
Description |
|---|---|
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 with the specified owner. If not specified, sp_iqdbspaceinfo displays information on tables for all users in the database. |
object_name |
Name of the table. If not specified, sp_iqdbspaceinfo displays information on all tables in the database. |
object_type |
Valid table objects. |
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.
The procedure returns no results if you specify an RLV dbspace.
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 subobject 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 subobject sizing information for all subobjects (using integer quantities with the suffix B, K, M, G, T, or P) 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 on the dbspace. |
object_id |
Global object ID of the object. |
id |
Table 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 system-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). |
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 subobjects in all tables in all dbspaces in the database:
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
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
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