sp_countmetadata

Displays the number of indexes, objects, or databases in the SAP ASE server.

Syntax

sp_countmetadata "configname" [, dbname]

Parameters

Examples

Usage

There are additional considerations when using sp_countmetadata:
  • sp_countmetadata displays the number of indexes, objects, databases, or partitions in the SAP ASE server, including the number of system databases such as model and tempdb.

  • Avoid running sp_countmetadata during SAP ASE peak times. It can cause contention on the sysindexes, sysobjects, sysdatabases, and syspartitions system tables.

  • You can run sp_countmetadata on a specified database if you want information on a particular database. However, when configuring caches for indexes, objects, databases, or partitions, run sp_countmetadata without the database_name option.

  • The information on memory returned by sp_countmetadata can vary by platform. For example, a database on an SAP ASE server for Windows could have a different sp_countmetadata result than the same database on Sun Solaris. Information on the number of user indexes, objects, databases, or partitions should be consistent, however.

  • sp_countmetadata does not include temporary tables in its calculation. Add 5 percent to the open objects value and 10 percent to the open indexes, open partitions value to accommodate temporary tables.

  • If you specify a nonunique fragment of “open indexes”, “open objects”, “open databases”, or “open partitions” for configname, sp_countmetadata returns a list of matching configuration parameter names with their configured values and current values. For example:

    sp_countmetadata "open"
    Configuration option is not unique.
     option_name                    config_value run_value
     ------------------------------ ------------ -----------
     curread change w/ open cursors           1            1
     number of open databases                12           12
     number of open indexes                 500          500
     number of open objects                 500          500
     open index hash spinlock ratio         100          100
     open index spinlock ratio              100          100
     open object spinlock ratio             100          100

Permissions

The permission checks for sp_countmetadata differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be a user with manage server privilege.

Disabled

With granular permissions disabled, you must be a user with sa_role.

Auditing

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

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • 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

Related reference
sp_configure
sp_helpconfig
sp_monitorconfig