I work on a project that use Postgre SQL, and I have 0 experience in this regard. I hope to get help here. I need two SQL scripts for Postgre:
1. Get all databases, which I aquired already:
SELECT datname FROM pg_database WHERE datistemplate = false
This one is functional, it's ok.
2. Now, I need to find all tables under a specific database. This one I don't know how to achieve it. Can you help me here ? It is possible ?
For instance, using this SQL script:
SELECT 1, datname FROM pg_database WHERE datistemplate = false
I got:
mydb1
mydb2
postgres
So far, so good. Of course, mydb1 and mydb2 have been created by me, using:
create database mydb1
create database mydb2
Now, which SQL script to use, to retrieve all tables under a database only, but, most important, using SQL script only.
For instance, something like (pseudo-script):
SELECT table_name FROM information_schema.tables WHERE database = 'mydb1'
SELECT table_name FROM information_schema.tables WHERE database = 'mydb2'
Kindly thank you.
Flaviu.