Product DocumentationAppearance
sys.database
A system view that returns information about the databases in the system.
| Column Name | Data Type | Description |
|---|---|---|
| database_id | bigint | The unique ID for the database. |
| name | text | Name of the database. (Note that the yellowbrick database row contains information about some internal tables.) |
| owner_id | bigint | Unique ID of the user who owns the database. Join to sys.user to get the name of the user. |
| encoding | text | Database encoding; either LATIN9 or UTF8. |
| collation | text | Database collation type; currently always C. |
| is_readonly | boolean | Whether the database is in READONLY mode. See HOT_STANDBY and READONLY Modes. |
| readonly_reason | text | Why the database is in READONLY mode. The database may have been set to READONLY explicitly by a database administrator. The system may put a database in this mode if the connection between the primary and secondary appliances is lost (for example, because of a network failure), or if the target database for replication is not in HOT_STANDBY mode. |
| is_hot_standby | boolean | Whether the database is in HOT_STANDBY mode. See HOT_STANDBY and READONLY Modes. |
| access_privileges | text | User access privileges defined for the database, using the following format: role=privileges/dbownerThe abbreviations are explained under Definition of Access Privileges. Alternatively, you can use the HAS_DATABASE_PRIVILEGE function to get information about access privileges. |
| table_count | bigint | Number of user tables and temporary tables in the database. |
| rows_columnstore | bigint | Total number of rows in the column store for this database (backend storage). |
| rows_rowstore | bigint | Total number of rows in the row store for this database (rows not yet flushed). |
| compressed_bytes | bigint | Total number of compressed bytes in the database. See also sys.table_storage |
| uncompressed_bytes | bigint | Total number of uncompressed bytes in the database. See also sys.table_storage |
Examples
Return information for a database named newpremdb:
premdb=# select * from sys.database where name = 'newpremdb';
-[ RECORD 1 ]------+---------
database_id | 16395
name | newpremdb
owner_id | 10
encoding | LATIN9
collation | C
is_readonly | f
readonly_reason | [NULL]
is_hot_standby | f
access_privileges | [NULL]
table_count | 5
rows_columnstore | 2203486
rows_rowstore | 8681
compressed_bytes | 69206016
uncompressed_bytes | 91051612Return information about any readonly databases:
premdb=# select database_id, name, is_readonly, readonly_reason, rows_columnstore from sys.database where is_readonly='t';
database_id | name | is_readonly | readonly_reason | rows_columnstore
-------------+--------+-------------+--------------------------------------------------------------------------------------------+------------------
31384 | repldb | t | Replication topology validation failure. Target database premdb_yb95 is not in HOT_STANDBY | 25944130936
(1 row)Return access privileges for the premdb database:
yellowbrick=# select name, access_privileges from sys.database where name='premdb';
name | access_privileges
--------+--------------------------
premdb | =Tc/ybrick1 +
| ybrick1=CTcpqQuO/ybrick1+
| bobr=CTcpqQuO/ybrick1 +
| bar=uO/ybrick1 +
| yb007=C/ybrick1 +
| yb008=q/ybrick1
(1 row)In this example, ybrick1 is the database owner. The users and roles with privileges on this database are bobr, bar, yb007, and yb008.
Definition of Access Privileges
The following abbreviations are used in the access_privileges column:
| Abbreviation | Access Privilege | Relevant Object Types |
|---|---|---|
| a (append) | INSERT | Tables, views, |
| r (read) | SELECT | Tables, views, sequences, |
| w (write) | UPDATE | Tables, views, sequences, |
| d | DELETE | Tables |
| D | TRUNCATE | Tables |
| x | REFERENCES | Tables (create a foreign-key constraint), views |
| X | EXECUTE | Stored procedures |
| U | USAGE | Schemas, sequences |
| C | CREATE | Schemas; databases; roles on system |
| T | CREATE TEMPORARY | Databases |
| c | CONNECT | Databases |
| e | ENCRYPT | Keys |
| E | DECRYPT | Keys |
| h | HMAC | Keys (privilege to run the HMAC_KS function with a key as input to the function) |
| b | CREATE DATABASE | System |
| p | EXPLAIN QUERY | Databases |
| q | VIEW QUERY TEXT | Databases |
| Q | TRACE QUERY | Databases |
| A | ALTER ANY ROLE | System |
| B | DROP ANY ROLE | System |
| u | BACKUP | Databases |
| O | RESTORE, RESTORE ANY DATABASE | RESTORE on databases, RESTORE ANY DATABASE on system |
Parent topic:System Views