SVV_REDSHIFT_COLUMNS - Amazon Redshift

SVV_REDSHIFT_COLUMNS

Use SVV_REDSHIFT_COLUMNS to view a list of all columns that a user has access to. This set of columns includes the columns on the cluster and the columns from datashares provided by remote clusters.

SVV_REDSHIFT_COLUMNS is visible to all users. Superusers can see all rows; regular users can see only metadata to which they have access.

Table columns

Column name Data type Description
database_name varchar(128) The name of the database where the table containing the columns exists.
schema_name varchar(128) The name of the schema for the table.
table_name varchar(128) The name of the table.
column_name varchar(128) The name of a column.
ordinal_position integer

The position of the column in the table.

data_type varchar(32) The data type of the column.
column_default varchar(4000)

The default value of the column.

is_nullable varchar(3)

A value that defines whether a column is nullable. Possible values are yes, no, and " " (an empty string that represents no information).

encoding varchar(128) The encoding type of the column.
distkey boolean A value that is true if this column is the distribution key for the table, and false otherwise.
sortkey integer

A value that specifies the order of the column in the sort key.

If the table uses a compound sort key, then all columns that are part of the sort key have a positive value that indicates the position of the column in the sort key.

If the table uses an interleaved sort key, then each column that is part of the sort key has a value that is alternately positive or negative. Here, the absolute value indicates the position of the column in the sort key.

If sortkey is 0, the column isn't part of a sort key.

column_acl varchar(128) A string that defines the permissions for the specified user or user group for the column.
remarks varchar(256) Remarks.

Sample query

The following example returns the output of SVV_REDSHIFT_COLUMNS.

SELECT * FROM svv_redshift_columns WHERE database_name = 'tickit_db' AND TABLE_NAME = 'tickit_sales_redshift' ORDER BY COLUMN_NAME, TABLE_NAME, database_name LIMIT 5; database_name | schema_name | table_name | column_name | ordinal_position | data_type | column_default | is_nullable | encoding | distkey | sortkey | column_acl | remarks --------------+-------------+-----------------------+-------------+------------------+-----------+----------------+-------------+----------+---------+---------+-------------+-------- tickit_db | public | tickit_sales_redshift | buyerid | 4 | integer | | NO | az64 | False | 0 | | tickit_db | public | tickit_sales_redshift | commission | 9 | numeric | (8,2) | YES | az64 | False | 0 | | tickit_db | public | tickit_sales_redshift | dateid | 6 | smallint | | NO | none | False | 1 | | tickit_db | public | tickit_sales_redshift | eventid | 5 | integer | | NO | az64 | False | 0 | | tickit_db | public | tickit_sales_redshift | listid | 2 | integer | | NO | az64 | True | 0 | |