USER MANUALS

GET_DATABASES

Description

The stored procedure GET_DATABASES returns information about databases. Each row represents a database.

Syntax

GET_DATABASES (
   input_db_name : text
)

  • If no parameter is passed to the procedure, it returns information about all databases in the server in which the invoker user has rights to access.

  • If the parameter input_db_name is passed to the procedure, it only returns information about the specified database.

The procedure returns this fields:

  • db_name: name of the database.

  • description: description of the database.

  • charset_default: the possible values are:

    • true: if the database charset is set to Default.

    • false: otherwise

  • charset: the possible values are:

    • restricted: if the database charset is set to Restricted (case-insensitive).

    • unicode: if the database charset is Restricted (case-sensitive).

  • cost_optimization_default: the possible values are:

    • true: if the database setting Automatic cost based optimization of queries is set to Default.

    • false: otherwise.

  • cost_optimization: the possible values are:

    • on: if the automatic cost based optimization of queries is enabled in the database.

    • off: if the automatic cost based optimization of queries is disabled in the database.

  • query_simplification_default: the possible values are:

    • true: if the database setting Automatic simplification of queries is set to Default.

    • false: otherwise.

  • query_simplification: the possible values are:

    • on: if the automatic query simplification is enabled in the database.

    • off: if the automatic query simplification is disabled in the database.

  • summary_rewrite_default: the possible values are:

    • true: if the database setting Use summaries for query acceleration is set to Default.

    • false: otherwise.

  • summary_rewrite: the possible values are:

    • on: if the usage of summaries for query acceleration is enabled in the database.

    • off: if the usage of summaries for query acceleration is disabled in the database.

  • vcs: the possible values are:

    • unversioned: if version control is disabled in the database.

    • <vcs system name>: if version control is enabled, the name of the version control system used in the database.

  • authentication: the possible values are:

    • local: if the database authentication is Global authentication settings.

    • ldap: if the database authentication is Custom (with LDAP).

  • odbc_authentication: the possible values are:

    • normal: if the database ODBC authentication is not configured.

    • kerberos: if the database ODBC authentication is configured using Kerberos.

  • cache_default: the possible values are:

    • true: if the database cache configuration is set to Default.

    • false: otherwise.

  • cache: the possible values are:

    • on: if the cache is enabled in the database.

    • off: if the cache is disabled in the database.

  • memory_config_default: the possible values are:

    • true: if the database memory usage configuration is set to Default.

    • false: otherwise.

  • check_view_restrictions_default: the possible values are:

    • true: if the database check view restrictions configuration is not changed from the server default.

    • false: otherwise.

  • check_view_restrictions: the possible values are:

    • always: if the database is configured to always check the view restrictions.

    • direct_queries_only: if the database is configured to check the view restrictions only when a direct query is made to the database.

The values of charset_default, cost_optimization_default, query_simplification_default, summary_rewrite_default, memory_config_default and check_view_restrictions_default will be true only if the database is configured with the property Default on the database configuration. Any other value will lead to the procedure returning false, even if the value of the default server configuration is the same as the database configuration.

Privileges Required

The output of this procedure changes depending on the privileges granted to the user that runs it. If the user is not an administrator user the procedure only returns information about the databases where the user has (at least) Connect privileges.

  • If the user has administrator privileges over the database, the procedure will return all the information specified on the previous section.

  • If the user has only connection privileges over the database, the procedure will return all the fields set to null, except for the fields db_name and description.

Examples

Example 1

SELECT *
FROM GET_DATABASES();

This returns one row for each database the caller user has access.

Example 2

SELECT *
FROM GET_DATABASES()
WHERE input_db_name = 'employees';

This returns one row with data of the database “employees”.

Example 3

SELECT db_name
FROM GET_DATABASES()
WHERE cost_optimization = 'on';

This returns the databases in which the automatic cost optimization of queries is enabled.

Add feedback