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. |
| readonly_reason | text | Why the database is in READONLY mode. The database may have been set to READONLY explicitly by a database administrator. |
| is_hot_standby | boolean | Whether the database is in HOT_STANDBY mode. |
| access_privileges | text | User access privileges (ACLs) defined for the database, using the following format: role=privileges/dbownerYou 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_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 compressed bytes of actual, live user data on the workers. See also sys.table_storage |
| snapshot_backup_bytes | bigint | Backup 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_bytes | bigint | Deleted and updated system data on the workers, which is kept in specialized internal data blocks. |
| reclaimable_bytes | bigint | Deleted or updated user data on the workers that will be freed up in the background. |
| uncompressed_bytes | bigint | Total uncompressed bytes of actual, live user data on the workers. See also sys.table_storage |
| max_size_bytes | bigint | Maximum size of the database set by the quota. NULL if no disk usage limit is set. |
| default_location | text | Default storage location for the database (currently, always LOCAL). |
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 | LOCALReturn 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 | 37748736In 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.
Parent topic:System Views