Product Documentation

Appearance

All platforms

sys.database ​

A system view that returns information about the databases in the system.

Column NameData TypeDescription
database_idbigintThe unique ID for the database.
nametextName of the database. (Note that the yellowbrick database row contains information about some internal tables.)
owner_idbigintUnique ID of the user who owns the database. Join to sys.user to get the name of the user.
encodingtextDatabase encoding; either LATIN9 or UTF8.
collationtextDatabase collation type; currently always C.
is_readonlybooleanWhether the database is in READONLY mode.
readonly_reasontextWhy the database is in READONLY mode. The database may have been set to READONLY explicitly by a database administrator.
is_hot_standbybooleanWhether the database is in HOT_STANDBY mode.
access_privilegestextUser access privileges (ACLs) defined for the database, using the following format:
role=privileges/dbowner


You can use the HAS_DATABASE_PRIVILEGE function to get information about database ACLs. For a complete list of ACLs and their abbreviations, see Abbreviations for ACLs.
table_countbigintNumber of user tables and temporary tables in the database.
rows_columnstorebigintTotal number of rows in the column store for this database (backend storage).
rows_rowstorebigintTotal number of rows in the row store for this database (rows not yet flushed).
compressed_bytesbigintTotal compressed bytes of actual, live user data on the workers. See also sys.table_storage
snapshot_backup_bytesbigintBackup and restore system data stored in internal system tables. Managed by the backups and only decreases when full backups occur. Not supported in this release.
delete_info_bytesbigintDeleted and updated system data on the workers, which is kept in specialized internal data blocks.
reclaimable_bytesbigintDeleted or updated user data on the workers that will be freed up in the background.
uncompressed_bytesbigintTotal uncompressed bytes of actual, live user data on the workers. See also sys.table_storage
max_size_bytesbigintMaximum size of the database set by the quota. NULL if no disk usage limit is set.
default_locationtextDefault storage location for the database (currently, always LOCAL).

Note: Aggregations in sys.database view are calculated based on the user's access privileges to the underlying tables. Tables where the user lacks SELECT privileges on any column are excluded from the aggregation.

Examples ​

Return information for a database named premdb:

premdb=# select * from sys.database where name = 'premdb';
-[ RECORD 1 ]---------+---------
database_id           | 16634
name                  | premdb
owner_id              | 16007
encoding              | LATIN9
collation             | C
is_readonly           | f
readonly_reason       | [NULL]
is_hot_standby        | f
access_privileges     | [NULL]
table_count           | 5
rows_columnstore      | 100
rows_rowstore         | 8681
compressed_bytes      | 4194304
snapshot_backup_bytes | 0
delete_info_bytes     | 0
reclaimable_bytes     | 0
uncompressed_bytes    | 3314
max_size_bytes        | [NULL]
default_location      | LOCAL

Return information about any read-only 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.

To return the total space usage on the workers for a given database, add the values of compressed_bytes, delete_info_bytes, and snapshot_backup_bytes:

premdb=# select compressed_bytes, delete_info_bytes, snapshot_backup_bytes,
compressed_bytes+delete_info_bytes+snapshot_backup_bytes as total_space 
from sys.database where name = 'premdb';
-[ RECORD 1 ]---------+---------
compressed_bytes      | 33554432
snapshot_backup_bytes | 4194304
delete_info_bytes     | 0
total_space           | 37748736

In this example, the total space usage is 37748736 bytes.

The following example demonstrates database visibility for a new user. By default, new users have CONNECT and TEMP privilege on all databases through membership in PUBLIC:

premdb=# create user noprivs;
CREATE ROLE
premdb=# alter user noprivs password 'noprivs';
ALTER ROLE
premdb=# \c premdb noprivs
Password for user noprivs: 
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "premdb" as user "noprivs".
premdb=> select database_id, name from sys.database                                                                                                             ;
 database_id |         name          
-------------+-----------------------
       4400 | yellowbrick
      16392 | yellowbrick_test
      16393 | yellowbrick_test_utf8
      19123 | premdb
(4 rows)

To limit visibility for the user noprivs, use a REVOKE command:

premdb=> \c premdb yellowbrick
Password for user yellowbrick: 
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "premdb" as user "yellowbrick".
premdb=# revoke connect, temp on database yellowbrick, yellowbrick_test, yellowbrick_test_utf8 from public;
REVOKE
premdb=# \c premdb noprivs
Password for user noprivs: 
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "premdb" as user "noprivs".
premdb=> select database_id, name from sys.database                                                                                                             ;
 database_id |  name  
-------------+--------
      19123 | premdb
(1 row)

Note that this REVOKE command applies to all users who belong to PUBLIC, not just the new user noprivs.