sp_iqdbspaceinfo Procedure

Displays the size of each object and subobject used in the specified table or join index.

Syntax

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

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Usage

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

Description

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.

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 (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 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).

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.

You can use this procedure in a mulitplex environment. See Using Sybase IQ Multiplex > Multiplex Reference > System Procedures > sp_iqdbspaceinfo Procedure.

Examples

Displays the size of all objects and sub-objects in all tables in all dbspaces in the database:

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