SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for AWS Amazon Web Services, Redshift, AWS Lambda Functions, S3 Buckets, VPC, EC2, IAM

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.

list of schemas on Amazon Redshift 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.

 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.

SQL query to list schemas on Redshift database

rdsdb is the system user which is the owner of system schemas created in every Redshift database.


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.