sp_countmetadata

Description

Displays the number of indexes, objects, or databases in Adaptive Server.

Syntax

sp_countmetadata "configname" [, dbname]

Parameters

configname

is either “number of open databases”, “number of open objects”, or “number of open indexes”, or “number of open partitions”.

dbname

is the name of the database on which to run sp_countmetadata. If no database name is given, sp_countmetadata provides a total count for all databases.

Examples

Example 1

Reports on the number of user objects in Adaptive Server. Use this value to set the number of objects allowed in the database, plus space for additional objects and temporary tables:

sp_configure "number of open objects", 310
sp_countmetadata "open objects"
There are 283 user objects in all database(s), requiring 
117.180 Kbytes of memory. The 'open objects' 
configuration parameter is currently set to a run value 
of 500.

Example 2

Reports on the number of indexes in Adaptive Server:

sp_countmetadata "open indexes", pubs2
There are 21 user indexes in pubs2 database(s), 
requiring 8.613 kbytes of memory. The 'open indexes' 
configuration parameter is currently set to 600.

Usage

Permissions

Only a System Administrator or the Database Owner can execute sp_countmetadata.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

System procedures sp_configure, sp_helpconfig, sp_monitorconfig