How to get the total number of tables in postgresql?

Question

Is there any way by which I can get the total number of tables in a Postgresql database? The postgresql version I'm using is PostgreSQL 8.4.14.

To explore what's going on in only one database I usually use `\d`. With this you can list the total number of tables, views and the sequences also. — omar, May 05 '15 at 17:47

score 87 · Accepted Answer · edited Feb 14 '23 at 18:40

87

select count(*)
from information_schema.tables;

Or if you want to find the number of tables only for a specific schema:

select count(*)
from information_schema.tables
where table_schema = 'public';

edited Feb 14 '23 at 18:40

Fellipe Sanches

answered Dec 18 '12 at 12:41

What if we want to count the number of the tables ? I know we can see the number but i want to use this number in a sql statement ? – Capan Nov 07 '17 at 19:26
1

Use `where table_type = 'BASE TABLE'` to exclude Views and Temporary Tables from the results. – Fellipe Sanches Feb 14 '23 at 18:56

score 21 · Answer 2 · answered Dec 18 '12 at 11:59

Just try to search in pg_stat... tables or information_schema you can find there very useful informations about your database.
Example:

select * from  pg_stat_user_tables ;
select count(*) from  pg_stat_user_tables ; 
select * from  pg_stat_all_tables ;

score 3 · Answer 3 · answered May 24 '21 at 08:47

3

If you want to get just the number of tables (without views) then:

select count(*) from information_schema.tables where table_type = 'BASE TABLE';

Or you can also filter by schema name by adding the where condition like:

table_schema = 'public'

answered May 24 '21 at 08:47

Popa Andrei

score 0 · Answer 4 · answered Jan 19 '23 at 01:00

0

Try this, it'w work for me

select t.table_catalog,  t.table_schema, t.table_name  from information_schema.tables t where  t.table_catalog = 'database_name';

answered Jan 19 '23 at 01:00

Ari Anggoro

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 20 '23 at 22:08

score -5 · Answer 5 · edited Oct 21 '16 at 10:29

-5

select Count(*) from sys.tables

edited Oct 21 '16 at 10:29

Bhargav Rao

answered Oct 21 '16 at 10:12

pipaliya uttam

2

This should work for SQL Server, right? Not PostgreSQL. – xnakos Oct 21 '16 at 10:44

5 Answers5