How to get the total number of tables in postgresql?
46,069
Solution 1
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';
Solution 2
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 ;
Solution 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'
Related videos on Youtube
Author by
harry
Updated on July 09, 2022Comments
-
harry almost 2 years
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.
-
omar about 9 yearsTo 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.
-
-
xnakos over 7 yearsThis should work for SQL Server, right? Not PostgreSQL.
-
Capan over 6 yearsWhat 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 ?