Getting All Tables Information in Redshift

According to the AWS Document , Redshift provides PG_TABLE_DEF table that contains all the table information, and you can query that table like

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
SELECT DISTINCT
  schemaname,
  tablename,
  column,
  type,
  encoding,
  distkey,
  sortkey,
  notnull
FROM
  PG_TABLE_DEF
WHERE 1=1
  AND schemaname in ('<SCHEMA_NAME>')
ORDER BY
  schemaname ASC,
  tablename ASC
;

However, this only returns all the information in the public schema, I assume there maybe some permission problem with my account.

Fortunately, there is another approach to achieve same goal by using the information_schema.tables and you can do something like

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
SELECT DISTINCT
  table_catalog,
  table_schema,
  table_name,
  table_type,
  self_referencing_column_name,
  reference_generation,
  user_defined_type_catalog,
  user_defined_type_schema,
  user_defined_name
FROM
  information_schema.tables
WHERE 1=1
  AND table_schema in ('<SCHEMA_NAME>')
ORDER BY
  table_schema ASC,
  table_name ASC
;

If you want to know more information in more lower granularity, there is an information_schema.columns table that contains column-level information.

Reference#

Reference