Redshift Observatory

System Table Tracker

System view information_schema.tables version 1.0.41533 / 2022-09-18

schema name column data type
information_schema tables reference_generation character_data
information_schema tables self_referencing_column_name sql_identifier
information_schema tables table_catalog sql_identifier
information_schema tables table_name sql_identifier
information_schema tables table_schema sql_identifier
information_schema tables table_type character_data
information_schema tables user_defined_name sql_identifier
information_schema tables user_defined_type_catalog sql_identifier
information_schema tables user_defined_type_schema sql_identifier

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