List Schemas created in Amazon Redshift Database
SQL developers can query on Amazon Redshift databases to get list of database schemas created on a target Redshift database. If SQL programmers create dynamic queries to build database solutions on Amazon Redshift databases, schema list of a database is frequently required. As shown in this tutorial, database developers can use SQL Select statements to get the list of database schemas.
SQL developers can use following simple SQL query to get the list of database schemas from pg_namespace system table from pg_catalog schema.
select * from pg_catalog.pg_namespace order by oid;
The output of above SQL query executed on sample Amazon Redshift database sampledb, shows the list of database schemas on target database. As can be seen easily, the schema owner column "nspowner" with value equals to 1 points to Amazon Redshift system schemas. For example the database schemas with namespace owner equals to 100 are customer namespaces or customer schemas on target sampledb database.
If the SQL developers want to display additional data about the schema owner, above SQL Select statement can be improved by adding pg_user table in JOIN statement as seen in following SQL script.
select
s.nspname as schema_name,
s.oid as schema_id,
u.usename as owner
from pg_catalog.pg_namespace as s
join pg_catalog.pg_user as u
on u.usesysid = s.nspowner
order by s.nspname;
The result of the SQL query is as follows which includes additional information besides the list of Redshift database schema list.
rdsdb is the system user which is the owner of system schemas created in every Redshift database.