SVV_ALL_SCHEMAS - Amazon Redshift
Table columnsSample query
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

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    |            |                 |