sp_countmetadata

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 the SAP ASE 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 the SAP ASE 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

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.

Setting	Description
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:

Information	Values
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