Get System Information

  • concept
    +
    SQL++ has a system catalog that stores metadata about a database. The system catalog is a namespace called system.

    There is a keyspace for each type of artifact. The keyspace names are plural in order to avoid conflicting with SQL++ keywords.

    Logical Hierarchy

    SQL++ has the following artifacts:

    Datastores

    Datastores are similar to sites. A datastore is a database deployment, for example, a server cluster, cloud service, or mobile installation. It is analogous to a relation database instance.

    Namespaces

    Namespaces are similar to pools. A namespace is a unit of authorization, resource allocation, and tenancy. It is analogous to a relational database or schema. Currently, only the default and system namespaces are available.

    Buckets

    A bucket is the fundamental space for storing data in Couchbase Server. Each bucket contains at least one scope by default, and you can create more scopes as required. It is analogous to a relational database table space or file group.

    Scopes

    A scope is a set of one or more collections. Each scope contains at least one collection by default, and you can create more collections as required. It is analogous to a group of relational database tables.

    Collections

    A collection is a set of documents that may vary in structure. It is a unit of authorization and resource allocation. It is analogous to a relational database table.

    Keyspaces

    Keyspaces map to collections in Couchbase Server. A keyspace may be referred to using a full keyspace reference. For example, default:`travel-sample`.inventory.airline refers to the airline collection in the inventory scope in the travel-sample bucket. A keyspace may also be referred to using a partial keyspace reference, comprising just the collection name. In this case, the namespace, bucket, and scope must be implied by the current query context.

    For compatibility with legacy versions of Couchbase Server, the name of the scope and collection may be omitted when referring to the default collection in the default scope within a bucket. So for example, we may refer to the travel-sample keyspace, meaning the default collection in the default scope in the travel-sample bucket.

    The term is also used to refer to any of the catalogs in the system namespace.

    Indexes

    An index on a keyspace. It is analogous to a relational database index. Types of indexes include b-tree (ForestDB or MOI) and view indexes.

    Dual

    The dual keyspace has been added for evaluating constant expressions. It contains a single entry with no attributes.

    Query Datastores

    You can query datastores using the system:datastores keyspace as follows:

    SELECT * FROM system:datastores

    This catalog contains the following attributes:

    Name Description Schema

    id
    required

    ID of the datastore.

    String

    url
    required

    URL of the datastore instance.

    String

    Query Namespaces

    You can query namespaces using the system:namespaces keyspace as follows:

    SELECT * FROM system:namespaces

    This catalog contains the following attributes:

    Name Description Schema

    id
    required

    ID of the namespace.

    String

    name
    required

    Name of the namespace.

    String

    datastore_id
    required

    ID of the datastore to which the namespace belongs.

    String

    Query Buckets

    You can query buckets using the system:buckets keyspace as follows:

    SELECT * FROM system:buckets

    This catalog contains the following attributes:

    Name Description Schema

    datastore_id
    required

    ID of the datastore to which the bucket belongs.

    String

    name
    required

    Name of the bucket.

    String

    namespace
    required

    Namespace to which the bucket belongs.

    String

    namespace_id
    required

    ID of the namespace to which the bucket belongs.

    String

    path
    required

    Path of the bucket.

    String

    Query Scopes

    You can query scopes using the system:scopes keyspace as follows:

    SELECT * FROM system:scopes

    This catalog contains the following attributes:

    Name Description Schema

    bucket
    required

    Bucket to which the scope belongs.

    String

    datastore_id
    required

    ID of the datastore to which the scope belongs.

    String

    name
    required

    Name of the scope.

    String

    namespace
    required

    Namespace to which the scope belongs.

    String

    namespace_id
    required

    ID of the namespace to which the scope belongs.

    String

    path
    required

    Path of the scope.

    String

    Querying system:scopes only returns named scopes — that is, non-default scopes. To return all scopes, including the default scopes, you can query system:all_scopes.

    Query Collections

    You can query collections using the system:keyspaces keyspace as follows:

    SELECT * FROM system:keyspaces

    This catalog contains the following attributes:

    Name Description Schema

    bucket
    optional

    For a named, non-default collection: Bucket to which the keyspace belongs.

    String

    datastore_id
    required

    ID of the datastore to which the keyspace belongs.

    String

    id
    required

    For the default collection in the default scope: ID of the bucket to which the keyspace belongs.


    For a named, non-default collection: ID of the keyspace.

    String

    name
    required

    For the default collection in the default scope: Bucket to which the keyspace belongs.


    For a named, non-default collection: Name of the keyspace.

    String

    namespace
    required

    Namespace to which the keyspace belongs.

    String

    namespace_id
    required

    ID of the namespace to which the keyspace belongs.

    String

    path
    required

    Path of the keyspace.

    String

    scope
    optional

    For a named, non-default collection: Scope to which the keyspace belongs.

    String

    Querying system:keyspaces only returns non-system keyspaces. To return all keyspaces, including the system keyspaces, you can query system:all_keyspaces.

    Query Indexes

    You can query indexes using the system:indexes keyspace as follows:

    SELECT * FROM system:indexes

    This catalog contains the following attributes:

    Name Description Schema

    bucket_id
    optional

    For an index on a named, non-default collection: ID of the bucket to which the index belongs.

    String

    condition
    optional

    Index filter, if present.

    String

    datastore_id
    required

    ID of the datastore to which the index belongs.

    String

    id
    required

    ID of the index.

    String

    index_key
    required

    List of index keys.

    String array

    is_primary
    required

    True if the index is a primary index.

    Boolean

    keyspace_id
    required

    For an index on the default collection in the default scope: ID of the bucket to which the index belongs.


    For an index on a named, non-default collection: ID of the keyspace to which the index belongs.

    String

    name
    required

    Name of the index.

    String

    metadata
    required

    Metadata for the index.

    Metadata object

    namespace_id
    required

    ID of the namespace to which the index belongs.

    String

    state
    required

    State of index.

    Example: online

    String

    using
    required

    Type of index.

    Example: gsi

    String

    Metadata

    Name Description Schema

    last_scan_time
    required

    The last scan timestamp of the index.

    String

    num_replica
    required

    The index replica count.

    String

    stats
    required

    Statistics for the index.

    Stats object

    Stats

    Name Description Schema

    last_known_scan_time
    required

    The index last scan time from the indexer, in UNIX Epoch format.

    Number

    Querying system:indexes only returns indexes on non-system keyspaces. To return all indexes, including indexes on system keyspaces, you can query system:all_indexes.

    Query Dual

    You can use dual to evaluate constant expressions.

    SELECT 2+5 FROM system:dual

    The query returns the result of the expression, 7 in this case.