Menu
I thought it is straightforward but I couldn't find a way to list all tables and their creators (or owners) in Redshift. Any help/insight is welcome.
607
View a list of table names. To view a list of all tables in a schema, you can query the PG_TABLE_DEF system catalog table. You can first examine the setting for search_path . The following example adds the SALES schema to the search path and shows all the tables in the SALES schema.
To view a list of all schemas, query the PG_NAMESPACE system catalog table: select * from pg_namespace; To view a list of tables that belong to a schema, query the PG_TABLE_DEF system catalog table. For example, the following query returns a list of tables in the PG_CATALOG schema.
There is a proper way to get table creation date and time in Redshift, that is not based on query log: SELECT TRIM(nspname) AS schema_name, TRIM(relname) AS table_name, relcreationtime AS creation_time FROM pg_class_info LEFT JOIN pg_namespace ON pg_class_info. relnamespace = pg_namespace. oid WHERE reltype !=
It was pg_tables table and here is the SQL:
select tablename, tableowner From pg_tables You can list Redshift tables, views and their owners by running this script:
SELECT n.nspname AS schema_name , pg_get_userbyid(c.relowner) AS table_owner , c.relname AS table_name , CASE WHEN c.relkind = 'v' THEN 'view' ELSE 'table' END AS table_type , d.description AS table_description FROM pg_class As c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace LEFT JOIN pg_description As d ON (d.objoid = c.oid AND d.objsubid = 0) WHERE c.relkind IN('r', 'v') ORDER BY n.nspname, c.relname ; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
