Redshift Observatory

System Table Tracker

System view pg_catalog.svv_redshift_tables version 1.0.59596 / 2023-11-12

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,
       CAST(pgu.usename AS varchar(128)) AS table_owner
FROM pg_namespace AS pns
     INNER JOIN pg_class AS pgc ON pgc.relnamespace = pns.oid
     LEFT JOIN pg_user AS pgu ON pgu.usesysid = pgc.relowner
     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))
  AND pns.nspname !~~ CAST('pg_temp%' AS text)

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,
       '' AS table_owner
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)
ORDER BY 1,
         2,
         3

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