Redshift Observatory

System Table Tracker

System view pg_catalog.svv_all_tables version 1.0.76913 / 2024-10-24

View Text

(((SELECT CAST(current_database() AS varchar(128)) AS database_name,
          CAST(pns.nspname AS varchar(128)) AS schema_name,
          CAST(pgc.relname AS varchar(128)) AS table_name,
          CAST(CASE
                 WHEN pns.nspname ~~ like_escape(CAST('pg!_temp!_%' AS text),
                                                 CAST('!' AS text))
                   THEN CAST('LOCAL TEMPORARY' AS text)
                 WHEN pgc.relkind = CAST('r' AS "char")
                   THEN CAST('TABLE' AS text)
                 WHEN pgc.relkind = CAST('v' AS "char")
                   THEN CAST('VIEW' AS text)
                 ELSE CAST(NULL AS text)
               END AS varchar) AS table_type,
          CAST(array_to_string(pgc.relacl,
                               CAST('~' AS text)) AS varchar(128)) AS table_acl,
          CAST(d.description AS varchar) AS remarks
   FROM pg_namespace AS pns
        INNER JOIN pg_class AS pgc ON pgc.relnamespace = pns.oid
        LEFT JOIN pg_description AS d ON pgc.oid = d.objoid
                                     AND d.objsubid = 0
   WHERE (pgc.relkind = CAST('r' AS "char")
       OR pgc.relkind = CAST('v' AS "char"))
     AND has_schema_privilege(CAST("current_user"() AS name),
                              CAST(pns.nspname AS text),
                              CAST('USAGE' AS text))
     AND (has_table_privilege(CAST("current_user"() AS name),
                              pgc.oid,
                              CAST('SELECT' AS text))
       OR has_any_column_privilege(CAST("current_user"() AS name),
                                   pgc.oid,
                                   CAST('SELECT' AS text)))
     AND (pns.nspname <> CAST('catalog_history' AS name)
      AND pns.nspname <> CAST('pg_toast' AS name)
      AND pns.nspname <> CAST('pg_internal' AS name))

   UNION ALL

   SELECT CAST(btrim(CAST(rs_tables.database_name AS text)) AS varchar(128)) AS database_name,
          CAST(btrim(CAST(rs_tables.schema_name AS text)) AS varchar(128)) AS schema_name,
          CAST(btrim(CAST(rs_tables.table_name AS text)) AS varchar(128)) AS table_name,
          CAST(btrim(CAST(rs_tables.table_type AS text)) AS varchar(128)) AS table_type,
          CAST(btrim(CAST(rs_tables.table_acl AS text)) AS varchar(128)) AS table_acl,
          CAST(btrim(CAST(rs_tables.remarks AS text)) AS varchar(128)) AS remarks
   FROM pg_get_shared_redshift_tables() AS rs_tables(database_name varchar,
                                                     schema_name varchar,
                                                     table_name varchar,
                                                     table_type varchar,
                                                     table_acl varchar,
                                                     remarks varchar))

  UNION ALL

  SELECT CAST(btrim(CAST(ext_tables.redshift_database_name AS text)) AS varchar(128)) AS database_name,
         CAST(btrim(CAST(ext_tables.schemaname AS text)) AS varchar(128)) AS schema_name,
         CAST(btrim(CAST(ext_tables.tablename AS text)) AS varchar(128)) AS table_name,
         CAST(CASE
                WHEN CAST(ext_tables.tabletype AS text) = CAST('EXTERNAL VIEW' AS text)
                  THEN CAST('EXTERNAL VIEW' AS text)
                ELSE CAST('EXTERNAL TABLE' AS text)
              END AS varchar) AS table_type,
         CAST(NULL AS unknown) AS table_acl,
         CAST(NULL AS unknown) AS remarks
  FROM pg_get_external_tables() AS ext_tables(es_or_edb_oid integer,
                                              redshift_database_name varchar,
                                              schemaname varchar,
                                              tablename varchar,
                                              location varchar,
                                              input_format varchar,
                                              output_format varchar,
                                              serialization_lib varchar,
                                              serde_parameters varchar,
                                              compressed integer,
                                              parameters varchar,
                                              tabletype varchar))

 UNION ALL

 SELECT CAST(btrim(CAST(ext_tables.redshift_database_name AS text)) AS varchar(128)) AS database_name,
        CAST(btrim(CAST(ext_tables.schemaname AS text)) AS varchar(128)) AS schema_name,
        CAST(btrim(CAST(ext_tables.tablename AS text)) AS varchar(128)) AS table_name,
        CAST(CASE
               WHEN CAST(ext_tables.tabletype AS text) = CAST('EXTERNAL VIEW' AS text)
                 THEN CAST('EXTERNAL VIEW' AS text)
               ELSE CAST('EXTERNAL TABLE' AS text)
             END AS varchar) AS table_type,
        CAST(NULL AS unknown) AS table_acl,
        CAST(NULL AS unknown) AS remarks
 FROM pg_get_external_database_tables() AS ext_tables(es_or_edb_oid integer,
                                                      redshift_database_name varchar,
                                                      schemaname varchar,
                                                      tablename varchar,
                                                      location varchar,
                                                      input_format varchar,
                                                      output_format varchar,
                                                      serialization_lib varchar,
                                                      serde_parameters varchar,
                                                      compressed integer,
                                                      parameters varchar,
                                                      tabletype varchar))

UNION ALL

SELECT CAST(btrim(CAST(ext_tables.databasename AS text)) AS varchar(128)) AS database_name,
       CAST(btrim(CAST(ext_tables.schemaname AS text)) AS varchar(128)) AS schema_name,
       CAST(btrim(CAST(ext_tables.tablename AS text)) AS varchar(128)) AS table_name,
       'EXTERNAL TABLE' AS table_type,
       CAST(NULL AS unknown) AS table_acl,
       CAST(NULL AS unknown) AS remarks
FROM pg_get_all_external_tables() AS ext_tables(databasename varchar,
                                                schemaname varchar,
                                                tablename varchar,
                                                esoid integer,
                                                location varchar,
                                                input_format varchar,
                                                output_format varchar,
                                                serialization_lib varchar,
                                                serde_parameters varchar,
                                                compressed integer,
                                                parameters varchar,
                                                tabletype varchar)

Home 3D Друк Blog Bring-Up Times Consultancy Cross-Region Benchmarks Email Forums IRC Mailing Lists Reddit Redshift Price Tracker Redshift Version Tracker Redshift Workbench System Table Tracker The Known Universe Twitter White Papers