postgresql: select all table from database;

this sql will return all tables name in your database;

select usename, relname, relkind, relhasrules, from pg_class, pg_user where usesysid=relowner and
(relkind =’r’ or relkind=’i’ or relkind=’s’) and relname !~’^pg_’ and
(relkind !=’i’ or relname !~’^xinx’) order by relname;

this sql has been tested on windows xp pro with postgresql 8 on it.

i use this sql when i was trying to develop ad-hoc report system.

Advertisements

2 thoughts on “postgresql: select all table from database;

  1. Adam Knight

    Thanks, that SQL was just what I needed – I found that my DB had a bunch of system tables called “SQL_*” in it (sql_features, sql_implementation_info, sql_languages, sql_packages, sql_parts, sql_sizing, sql_sizing_profiles) which I got rid of. Also, relkind = ‘i’ appears to include indexes and relkind = ‘s’ seems to add nothing? I took the liberty of modifying your script slightly to fit my needs:

    select usename, relname, relkind, relhasrules from pg_class, pg_user where usesysid=relowner and
    (relkind =’r’ /*or relkind=’i’ or relkind=’s’*/) and relname !~’^pg_’ and /*new bit*/ relname !~’^sql_’ and
    (relkind !=’i’ or relname !~’^xinx’) order by relname;

    This was on Postgres8.3.5 on MacOS 10.5.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s