Redshift Research Project

System Table Tracker

System view pg_catalog.svv_all_tables version 1.0.38698 / 2022-05-27

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))
    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(current_database() 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_external_tables() AS ext_tables(esoid integer,
                                             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