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

sp_countmetadata displays the number of indexes, objects, databases, or partitions in Adaptive Server, including the number of system databases such as model and tempdb.
Avoid running sp_countmetadata during Adaptive Server 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 Adaptive Server for Windows NT 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.

Granular permissions enabled	With granular permissions enabled, you must be a user with manage server privilege.
Granular permissions 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:

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