PostgreSQL query to list all table names?
PostgresqlPostgresql 9.2Information SchemaPostgresql Problem Overview
Is there any query available to list all tables in my Postgres DB.
I tried out one query like:
SELECT table_name FROM information_schema.tables
WHERE table_schema='public'
But this query returns views also.
How can i get only table names only, not views?
Postgresql Solutions
Solution 1 - Postgresql
What bout this query (based on the description from manual)?
SELECT table_name
FROM information_schema.tables
WHERE table_schema='public'
AND table_type='BASE TABLE';
Solution 2 - Postgresql
If you want list of database
SELECT datname FROM pg_database WHERE datistemplate = false;
If you want list of tables from current pg installation of all databases
SELECT table_schema,table_name FROM information_schema.tables
ORDER BY table_schema,table_name;
Solution 3 - Postgresql
Open up the postgres terminal with the databse you would like:
psql dbname (run this line in a terminal)
then, run this command in the postgres environment
\d
This will describe all tables by name. Basically a list of tables by name ascending.
Then you can try this to describe a table by fields:
\d tablename.
Hope this helps.
Solution 4 - Postgresql
Try this:
SELECT table_name
FROM information_schema.tables
WHERE table_schema='public' AND table_type='BASE TABLE'
this one works!
Solution 5 - Postgresql
SELECT table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema='public';
For MySQL you would need table_schema='dbName' and for MSSQL remove that condition.
Notice that "only those tables and views are shown that the current user has access to". Also, if you have access to many databases and want to limit the result to a certain database, you can achieve that by adding condition AND table_catalog='yourDatabase' (in PostgreSQL).
If you'd also like to get rid of the header showing row names and footer showing row count, you could either start the psql with command line option -t (short for --tuples-only) or you can toggle the setting in psql's command line by \t (short for \pset tuples_only). This could be useful for example when piping output to another command with \g [ |command ].
Solution 6 - Postgresql
select
relname as table
from
pg_stat_user_tables
where schemaname = 'public'
- This will not work if track_activities is disabled
select
tablename as table
from
pg_tables
where schemaname = 'public'
- Read more about pg_tables
Solution 7 - Postgresql
How about giving just \dt in psql? See https://www.postgresql.org/docs/current/static/app-psql.html.