CODEWITHSUNDEEP
amazon-redshift
kee
kee

Reputation: 11629

How to list all tables and their creators (or owners) in Redshift

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.

Upvotes: 38

Views: 77233

Answers (3)

igeis
igeis

Reputation: 31

If you are looking to search view and procedure source code from specific schemas, you can use the below query. I found it very useful.

SELECT
    n.nspname AS schema_name
    , 'view' AS object_type
    , c.relname AS object_name
    , pg_get_userbyid(c.relowner) AS object_owner
    , v.view_definition AS object_definition
FROM
    pg_class AS c
    LEFT JOIN pg_namespace n
        ON n.oid = c.relnamespace
    LEFT JOIN information_schema.VIEWS v
        ON c.relname = v.table_name
WHERE
    c.relkind IN ('v') /* view */
    AND n.nspname = '<schema_name>'
    AND REGEXP_INSTR(v.view_definition, 'regular expression search string here') > 0
UNION
SELECT
    n.nspname AS schema_name
    , 'procedure' AS object_type
    , p.proname AS object_name
    , pg_get_userbyid(p.proowner) AS object_owner
    , p.prosrc AS object_definition
FROM
    pg_catalog.pg_namespace n
    JOIN pg_catalog.pg_proc p
        ON pronamespace = n.oid
    JOIN pg_user b
        ON b.usesysid = p.proowner
WHERE
    n.nspname = '<schema_name>'
    AND REGEXP_INSTR(p.prosrc, 'regular expression search string here') > 0
ORDER BY 1,2,3,4

Upvotes: 0

LiriB
LiriB

Reputation: 822

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 ;

Upvotes: 24

kee
kee

Reputation: 11629

It was pg_tables table and here is the SQL:

select tablename, tableowner From pg_tables

Upvotes: 57

Related Questions