Redshift Observatory

System Table Tracker

System view information_schema.tables version 1.0.52931 / 2023-07-07

View Text

SELECT CAST(current_database() AS information_schema.sql_identifier) AS table_catalog,
       CAST(nc.nspname AS information_schema.sql_identifier) AS table_schema,
       CAST(c.relname AS information_schema.sql_identifier) AS table_name,
       CAST(CASE
              WHEN nc.nspname ~~ like_escape(CAST('pg!_temp!_%' AS text),
                                             CAST('!' AS text))
                THEN CAST('LOCAL TEMPORARY' AS text)
              WHEN c.relkind = CAST('r' AS "char")
                THEN CAST('BASE TABLE' AS text)
              WHEN c.relkind = CAST('v' AS "char")
                THEN CAST('VIEW' AS text)
              ELSE CAST(NULL AS text)
            END AS information_schema.character_data) AS table_type,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS self_referencing_column_name,
       CAST(CAST(NULL AS information_schema.character_data) AS information_schema.character_data) AS reference_generation,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS user_defined_type_catalog,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS user_defined_type_schema,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS user_defined_name
FROM pg_namespace AS nc, pg_class AS c, pg_user AS u
WHERE c.relnamespace = nc.oid
  AND u.usesysid = c.relowner
  AND (c.relkind = CAST('r' AS "char")
    OR c.relkind = CAST('v' AS "char"))
  AND (u.usename = CAST("current_user"() AS name)
    OR has_table_privilege(c.oid,
                           CAST('SELECT' AS text))
    OR has_table_privilege(c.oid,
                           CAST('INSERT' AS text))
    OR has_table_privilege(c.oid,
                           CAST('UPDATE' AS text))
    OR has_table_privilege(c.oid,
                           CAST('DELETE' AS text))
    OR has_table_privilege(c.oid,
                           CAST('RULE' AS text))
    OR has_table_privilege(c.oid,
                           CAST('REFERENCES' AS text))
    OR has_table_privilege(c.oid,
                           CAST('TRIGGER' AS text))
    OR has_any_column_privilege(c.oid,
                                CAST('SELECT' AS text))
    OR has_any_column_privilege(c.oid,
                                CAST('UPDATE' AS text)))

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