sp_iqdbspaceinfo procedure

Function

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

Syntax

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

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.

Table 7-18: 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 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.

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

Examples

Displays the size of all objects and sub-objects 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
0B       1.37M     0B           0B                 0B
0B       464K      0B           0B                 0B
0B       1.22M     0B           0B                 0B
0B       5.45M     24K          0B                 48K
48K      4.63M     24K          0B                 0B
0B       1.78M     24K          0B                 48K
0B       8.03M     24K          0B                 48K
0B       1.53M     24K          0B                 0B
0B       2.19M     24K          0B                 48K
192K     4.67M     24K          0B                 0B
0B       2.7M      24K          0B                 104K
0B       3.35M     24K          0B                 144K

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
0B       5.45M     24K          0B                 48K
48K      4.63M     24K          0B                 0B
0B       1.78M     24K          0B                 48K
0B       8.03M     24K          0B                 48K
0B       1.53M     24K          0B                 0B
0B       2.19M     24K          0B                 48K
192K     4.67M     24K          0B                 0B
0B       2.7M      24K          0B                 104K
0B       3.35M     24K          0B                 144K

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
0B       1.78M     24K          0B                 48K

See also

“sp_iqdbspace procedure” and “sp_iqindexinfo procedure”