sp_iqdbspaceinfo Procedure

Syntax

sp_iqdbspaceinfo [ dbspace-name ] [ , owner_name ] [ ,  object_name ] [ , object-type  ]

Parameters

All parameters are optional, and any parameter may be supplied independent of another parameter’s value.

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.

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.

Applies to

Simplex and multiplex.

Privileges

You must have EXECUTE privilege on the system procedure. You must also have one of the following system privileges:

BACKUP DATABASE
SERVER OPERATOR
MANAGE ANY DBSPACE

Remarks

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.

sp_iqdbspaceinfo Columns
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.

Example

Note: 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 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

Displays the size of all objects and subobjects 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 subobjects 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