SVV_ALL_SCHEMAS

Use SVV_ALL_SCHEMAS to view a union of Amazon Redshift schemas as shown in SVV_REDSHIFT_SCHEMAS and the consolidated list of all external schemas from all databases. For more information about Amazon Redshift schemas, see SVV_REDSHIFT_SCHEMAS.

SVV_ALL_SCHEMAS is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see Visibility of data in system tables and views.

Table columns

Column name	Data type	Description
database_name	varchar(128)	The name of the database where the schema exists.
schema_name	varchar(128)	The name of the schema.
schema_owner	integer	The user ID of the schema owner. For information about user IDs, see PG_USER_INFO.
schema_type	varchar(128)	The type of the schema. Possible values are external, local, and shared schemas.
schema_acl	varchar(128)	The string that defines the permissions for the specified user or user group for the schema.
source_database	varchar(128)	The name of the source database for external schema.
schema_option	varchar(256)	The options of the schema. This is an external schema attribute.

Sample query

The following example returns the output of SVV_ALL_SCHEMAS.

SELECT *
FROM svv_all_schemas
WHERE database_name = 'tickit_db'
ORDER BY database_name,
    SCHEMA_NAME;


 database_name |    schema_name     | schema_owner | schema_type | schema_acl | source_database | schema_option
---------------+--------------------+--------------+-------------+------------+-----------------+--------------- 
   tickit_db   |       public       |       1      |   shared    |            |                 |