List All Databases and Tables Using PostgreSQL
- PostgreSQL also called as Postgres, is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance.
- The primary functions are to store data securely and effective retrieval at the request of other software applications.
- It can handle workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.
- PostgreSQL is cross-platform and runs on many operating systems including
- Linux,
- FreeBSD,
- OS X,
- Solaris, and
- Microsoft Windows.
- PostgreSQL provides the concurrency management through a system known as multiversion concurrency control (MVCC), which gives each transaction a "snapshot" of the database, allowing changes to be made without being visible to other transactions until the changes are committed.
- This largely eliminates the read locks on the data, and ensures the database to maintain the ACID (atomicity, consistency, isolation, durability) properties.
- PostgreSQL offers three levels of transaction isolation:
- Read Committed,
- Repeatable Read and
- Serializable
Option 1 :
- Now am trying to show how the list all databases and tables using psql (PostgreSQL)
To list all the databases in the server, use the command
\list or \l
To list all the tables in current database, use the command
\dt
(Note: Before executing the above statement it’s advisable To execute the below query alter database <databasename> set search_path=data, public;The reason is, the tables inserted cannot be amended to the schema. Here is the technique to add the table into the schema)
- You will never see tables in other databases, these tables aren't visible. You have to connect to the correct database to see its tables (and other objects).
To switch databases :
\connect database_name
Option 2 :
To list down the database is by executing the query
SELECT datname FROM pg_database;
(or)
SELECT datname FROM pg_database
WHERE datistemplate = false;
- Login as pg_admin and execute the below query will provide we can list out the tables in the current database
(or)
SELECT table_schema,table_name
FROM information_schema.tables
ORDER BY table_schema,table_name;
(or)
SELECT *
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema = 'public'
ORDER BY table_type, table_name
Applies to sqlserver version :
- SQL Server 7.0 OLAP Tools
- SQL Server 2000 64-bit Edition