I work with a large Postgres install that has many hundreds of schemas. there are times that I need to find a list of tables and fields from a specific schema for a web application that I am developing.

This will return the list of table names within the giving schema:

select relname 
from pg_stat_user_tables 
WHERE schemaname='schema1';

Then to get the given field names from the table that I have chosen:

select column_name 
from information_schema.columns 
where table_name='mytable';

Tags: SQL

This entry was posted on Friday, February 8th, 2013 at 7:47 pm and is filed under Programming, SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.