Database

13-Jan-22 0:57

I need to get all database names, using SQL script only. I have tried:

SQL

Copy Code

SELECT datname FROM pg_database  WHERE datistemplate = false

and seem to work. I got:

SQL

Copy Code

mydb1
mydb2
postgres

mybd1 and mydb2 have been created by me. So far, so good. Now, I need to find all tables under every database, using SQL script only. Is it possible ?

Victor Nijegorodov13-Jan-22 7:05

13-Jan-22 7:05

Could it help you: sql - List all tables in postgresql information_schema - Stack Overflow ?

_Flaviu13-Jan-22 7:45

13-Jan-22 7:45

Thank you Victor. Obviosly, I don't understand something: if I do:

SQL

Copy Code

create database mydb3

and then I want to create a table under that db, how can I do that ?

Because any (dummy) table I create, seem to be any no database inside ...

P.S. I saw (and tried) that link ... thanks anyway.

Victor Nijegorodov13-Jan-22 7:57

13-Jan-22 7:57

I never used Postgre...
In MS SQL Server you usually use something like:

SQL

Copy Code

create database mydb3
go
use mydb3
CREATE TABLE <new_table_name>

In postgresql you should use something similar... Example: postgresql - How to switch databases in psql? - Stack Overflow

_Flaviu13-Jan-22 22:24

13-Jan-22 22:24

Hello Victor. As far as I understood, "use ..." is not valid command in Postgre SQL ... I have tried that.

Victor Nijegorodov13-Jan-22 22:28

13-Jan-22 22:28

_Flaviu wrote:
As far as I understood, "use ..." is not valid command in Postgre SQL

I know.
But did you try the

SQL

Copy Code

\connect

_Flaviu14-Jan-22 1:01

14-Jan-22 1:01

That command is a Postgre SQL cmd line command, I cannot run it as SQL script, I have tried that.

Thank you.

CHill6014-Jan-22 2:16

CHill60

14-Jan-22 2:16

If you are not using psql then how are you running your SQL?

_Flaviu14-Jan-22 2:47

14-Jan-22 2:47

Is about a C++ ODBC class which can run SQL scripts only.

Victor Nijegorodov14-Jan-22 3:05

14-Jan-22 3:05

Could you try to set the desired database in the connection string (PostgreSQL ODBC Driver (psqlODBC) Connection Strings - ConnectionStrings.com)?

Richard MacCutchan14-Jan-22 0:39

Richard MacCutchan

14-Jan-22 0:39

Did you try the link I gave you at Re: List all tables from a specific database - Database Discussion Boards[^]?

_Flaviu14-Jan-22 1:06

14-Jan-22 1:06

Yes, I have tried:

SQL

Copy Code

SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND 
    schemaname != 'information_schema';

but this script doesn't take into account a specific database. I don't understand how is relation between database and table, in Postgre SQL ...

Richard MacCutchan14-Jan-22 1:13

Richard MacCutchan

14-Jan-22 1:13

That is not what the tutorial suggests. The summary states:

Quote:
Summary: in this tutorial, you will learn how to show tables in PostgreSQL using psql tool and pg_catalog schema.

_Flaviu14-Jan-22 4:32

List all tables from a specific database

14-Jan-22 4:32

I realize that all available tables are below existing connection, which is the default database. And the default database must be choosen at the connection time. But yes, all databases could be listed in any connection.

Thank you for all your support.

modified 16-Jan-22 5:05am.

_Flaviu12-Jan-22 1:41

12-Jan-22 1:41

How can I list all tables from a specific database in PostGre SQL ?

Lets say I have 3 databases, and I have several tables in every one each of them. How can I retrieve the table names for every database I have, using SQL command ?

With following command:

SQL

Copy Code

SELECT datname FROM pg_database	WHERE datistemplate = false

SQL

Copy Code

I got:
db_name
post_flaviu
postgres
test

Now, how can I find all tables under eevry db from above ?

modified 12-Jan-22 7:16am.

Peter_in_278012-Jan-22 2:18

Peter_in_2780

12-Jan-22 2:18

Feeding "postgresql list tables in database" into your search engine will produce a zillion useful answers.

Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012

_Flaviu12-Jan-22 2:35

12-Jan-22 2:35

Yes, I did. But I haven't found any SQL to tell me the tables for a specific database only.

k505412-Jan-22 2:35

k5054

12-Jan-22 2:35

You can get Postgres to tell you how to do that using the -E (display hidden queries) option to psql. So for example

Shell

Copy Code

$ psql -E -l
********* QUERY **********
SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
<hr />

                                  List of databases
   Name    |  Owner   | Encoding  |   Collate   |    Ctype    |   Access privileges   
-----------+----------+-----------+-------------+-------------+-----------------------
 template0 | postgres | UTF8      | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres          +
           |          |           |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8      | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres          +
           |          |           |             |             | postgres=CTc/postgres
 test      | ebacon   | UTF8      | en_CA.UTF-8 | en_CA.UTF-8 | 
(3 rows)

Likewise for the

Copy Code

\dt<dt> command inside psql.  Then all you need to do is to figure out how to combine both queries into one.<br />

<div class="signature">Keep Calm and Carry On</div></dt>

_Flaviu12-Jan-22 2:59

12-Jan-22 2:59

Thank you. That query retrieve all databases. And for all tables from a database only ?

k505412-Jan-22 5:13

k5054

12-Jan-22 5:13

try

Copy Code

psql -E test -c "\dt". You can then take that output and combine with the previous one to get a listing of all tables across all databases.<br />

<div class="signature">Keep Calm and Carry On</div>

_Flaviu12-Jan-22 5:32