Displays the number of indexes, objects, or databases in Adaptive Server.
sp_countmetadata "configname" [, dbname]
is either "open indexes", "open objects", or "open databases".
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.
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.
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.
sp_countmetadata displays the number of indexes, objects, or databases 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, and sysdatabases 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, or databases, 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, or databases 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 value to accommodate temporary tables.
If you specify a nonunique fragment of "open indexes", "open objects", or "open databases" 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
Only a System Administrator or the Database Owner can execute sp_countmetadata.
System procedures sp_configure, sp_helpconfig, sp_monitorconfig
