SQL script to change the ownership of schema, table ,procedure in AWS Redshift Database

 

SQL script to change the ownership of schema, table ,procedure in AWS Redshift Database

1.change the ownership of schema

select 'alter schema ' ||nspname||' owner to "<new_schema_name>";' from pg_catalog.pg_namespace 

where nspname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and nspname not like 'pg_%';

2.change the ownership of table

select 'alter table   klg_nga_kla.'||schemaname||'."'||tablename||'" owner to "<new_schema_name>";'

from pg_catalog.pg_tables where schemaname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and schemaname not like 'pg_%';

3.change the ownership of procedure

SELECT 'alter  procedure '||n.nspname||'."'||p.proname||'" owner to "<new_schema_name>";'

    

FROM

    pg_catalog.pg_namespace n

JOIN pg_catalog.pg_proc p ON

    pronamespace = n.oid

join pg_catalog.pg_user b on

    b.usesysid = p.proowner

where

    nspname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and nspname not like 'pg_%';

--add argument on the procedue while changing the ownership